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