Try

select ao.*, ph.invDate,sum(pl.shipped) as shipped ;
	from allOrders ao ;
	left join pastinvoiceLines pl ;
		on (pl.itemNum = ao.item_num) ;
	LEFT join pastinvoices ph ;
		on (ph.invnum = pl.invnum) ;
		AND ph.poNum = ao.po_num ;
	group by poItem ;
	into cursor merged

>Hello all,
>
>The following SELECT statements grabs orders from an EDI subsystem:
>
>

>select oh.po_num, oh.request_d, ol.item_num, ol.prod_id1, ol.qty_order, ;
>	ol.price, (oh.po_num + ol.item_num) as poItem ;
>	from order_h oh ;
>	join order_l ol on (oh.order_num = ol.order_num) ;
>	where oh.request_d >= {^2001-11-01} ;
>		and oh.request_d <= {^2001-02-28} ;
>		and cust_num = "   116" ;
>	into cursor allOrders   && returns 5444 records
>

>
>
>Now, I need to create a query that contains all of the results from the above, joined with information from pastinvoice (headers) and pastinvoiceLines tables.
>
>One of the fields in the result needs to be the number of pieces shipped. It is possible that no invoices have been shipped against a given PO, in that case shipped needs to be zero.
>
>Here is the best I have been able to come up with:
>

>select ao.*, ph.invDate,sum(pl.shipped) as shipped ;
>	from allOrders ao ;
>	left join pastinvoiceLines pl ;
>		on (pl.itemNum = ao.item_num) ;
>	join pastinvoices ph ;
>		on (ph.invnum = pl.invnum) ;
>	where ;
>		ph.poNum = ao.po_num ;
>	group by poItem ;
>	into cursor merged
>

>
>This returns less than 5444 records, because some items have not been shipped. I need the final result to still have 5444 records. Also, this second query runs pretty slow (I think it joins everything before applying the where condition).
>
>I tried creating a view that joins pastInvoices and pastInvoiceLines, so I could join with that view in my second query, but opening the view takes a lifetime.
>
>Any ideas? This should be easy...
>
>Thanks,