>>I have four tables:
>>
>>Orders_header, Orders_detail, Inovoice_header, Invoice_detail.
>>
>>Making a long sad story short - some of the invoices don't have all the item details because the item wasnt shipped, but of course NOW someone needs a report showing the item anyway - with a quantity of zero. To make matters worse - it all needs to be done in one SQL Statement....which is where I'm stuck. Anoying because I've actually done this before - just can't remember how the heck I did it..grrrr
>>
>>So...tables look like this:
>>
>>Orders_header
>>-------------------
>>MasterNum N(10)
>>OrderNum N(10)
>>
>>Orders_detail
>>------------------
>>OrderNum N(10)
>>ItemID C(3)
>>LineItemNum N(3)
>>QuantityOrdered N(3)
>>
>>Invoice_header
>>--------------------
>>MasterNum N(10)
>>InvoiceNum N(10)
>>
>>Invoice_Detail
>>--------------------
>>InvoiceNum N(10)
>>ItemID C(3)
>>LineItemNum N(3)
>>QuantityShipped N(3)
>>
>>
>
>Victor,
>
>Try something like
>

>Select O.OrderNum, O.ItemID, O.MasterNum, O.LineItemNum, ;
>NVL(O.QuantityOrdered,00000000) as Ordered, NVL(I.QuantityShipped,00000000000) as Shipped 
>from (select O.MasterNum, OD.OrderNum, OD. other fields from Orders O 
>LEFT JOIN Orders_Details on O.OrderNum = OD.OrderNum) O 
>LEFT JOIN (select I.MasterNum, I.InvoiceNum, ID. fields from Invoice_header I 
>LEFT JOIN Invoice_Detail ID on I.InvoiceNum = ID.InvoiceNum) I
>ON O.MasterNum = I.MasterNum and O.ItemID = I.ItemID
>

>
>In other words, think about creating two separate cursors and join them, but instead of the cursors use them as derived tables.

ahhhh - that sort of jogs my memory now...I will play with this idea later today when I get a chance and see if I can get it to fly...

Thanks Naom !