AW: SQL: how to join header and detail records

I'd use the following query:



With TotDet as (Select OrderNo, Sum(DetQty) DetQTY

From OrderDet

Group By OrderNo)

Select Coalesce(H.OrderNo, D.OrderNo), HdrQty, DetQty

From OrderHdr h full outer join TotDet d on h.OrderNo = d.OrderNo

Where HdrQty <> DetQty or HdrQty is NULL or DetQty is NULL

;



Mit freundlichen Grüßen / Best regards



Birgitta Hauser



"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)

"If you think education is expensive, try ignorance." (Derek Bok)

"What is worse than training your staff and losing them? Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
Gesendet: Wednesday, 03.4 2013 20:21
An: Midrange Systems Technical Discussion
Betreff: Re: SQL: how to join header and detail records



Ok, there's a bug with:

Select orderno, totqty, sum(dtlqty) as dtlsum from orderheader join
orderdetail using (orderno) group by orderno, totqty -- put totqty in group
by to avoid MAX having totqty <> sum(dtlqty)



ORDERNO TOTQTY DTLSUM

2 12 10

1 10 10



After you figure out that one, let me throw another loop at you. Add
orderno 3 into the header with a TOTQTY=12 and no detail records.





Rob Berendt