Hello friends,

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.