I would like to discuss some ways of reporting, and their efficiency. Classical example: we have two tabels - orders and orderitems, where orders has for example two fields: clientno, orderno, and orderitems has orderno, itemno, qty fields.
For example we have to make a huge report that shows all orders and their corresponding items.
Whe have two options:
1. Have a join, that would return one table:
select <needed fields> from orders,orderitems where orders.orderno = orderitem.orderno order by orderno
this way we get redundant fields for every order.
2. Return a cursor with one field as a nested table that would contain all order items.
It looks like a more efficient way to me.
Please, share your thoughts on both ways to create such a report.
I've worked with MS ADO for quite a while, and such a report is easy to make using MS DataShape provider. I've read somewhere that in PL/SQL such things are done using CONNECT BY clause. Could you paste this statement for the problem above? If it's not CONNECT BY, please give a hint how to assign orderitems to nested table field
Thank you very much for your replies.
The CONNECT BY clause is typically used in a tree-structured query.
It sounds like you will have to create an object type that contains the nested table.
Could you please paste an example of CONNECT BY statement?
Using a plan_table created by utlxplan:
select lpad(' ',2*(level -1)) || level || '.' || nvl(position,0)
|| ' ' || operation || ' ' || options || ' ' || object_name || ' '
|| object_type || ' ' || decode(id,0,statement_id || ' Cost = ' || position)
|| ' ' || object_node "Query Plan"
start with id=0 and statement_id = '&Statement_id'
connect by prior id=parent_id
and statement_id = '&Statement_id'
Click Here to Expand Forum to Full Width