On 03 Apr 2013 09:52, Stone, Joel wrote:
I have an order header and detail file with one to many.
The order header has TOTQTY, which is the sum of all detail
quantities.
The order detail has DTLQTY, which is one line's qty
One order's quantity, with one /line/ per order.
create table OHdr
( ordno dec(7), primary key (ordno), TotQty dec(17)
)
;
create table ODtl
( ordno dec(7), DtlQty dec(13)
, foreign key (ordno) references OHdr (ordno)
on delete cascade on update restrict
)
;
If I do a join of the two files and there is one HDR and five DTL
records, there will now be FIVE rows.
I want to compare the sum of the DTLQTY to the single TOTQTY value.
If those are the only columns of importance to the query, then the
following query should suffice to compare the SUM aggregate and that
aggregate value which is [oddly] stored in the header:
select h.ordno, h.TotQty, dec( sum(DtlQty), 17) as DtlSum
from OHdr H
/* left or inner */ join ODtl D
on H.ordno = D.ordno
group by h.ordno , h.totqty
having h.totqty <> dec( sum(dtlqty), 17)
I can use SUM(DTLQTY) to provide the sum of the detail.
What is a good way to provide the TOTQTY? I don't want to use
SUM(TOTQTY) as this would give a result 5 times the correct value.
Is MAX(TOTQTY) what people generally use?
But that seems to be misleading since I am not really looking for the
max value, as all rows contain the same value for a header column.
In the 1-to-many, the value of TOTQTY is going to be the same for
every order [i.e. there is only one TotQty for each order], so there is
no requirement to use MAX or MIN, just include TOTQTY in the grouping.
Nonetheless, the MIN or the MAX are reasonable to accomplish what is
required in a grouping when necessary, but their use indeed can be
deceptive to the uninitiated. But the TABLE is already poorly defined,
relationally, which makes such nonsensical requests more likely.
Should I be grouping the detail PRIOR to the join, so there is only
ONE header value?
I am not sure about the implication for *prior* but the SELECT can be
of the summary and the join together; e.g. perhaps one of:
/* join using a NTE */
select H.ordno, H.TotQty, D.SumQty
from OHdr H
/* left or inner */ join
( select ordno, dec( sum(DtlQty), 17 ) as SumQty
from ODtl
group by ordno ) D
on H.ordno = D.ordno
/* an implied join using a correlated scalar subselect */
select H.ordno, H.TotQty
, ( select dec( sum(DtlQty), 17 )
from ODtl D
where D.ordno = H.ordno ) as SumQty
from OHdr H
The latter is better when there is a specific set from OHdr being
selected.
But it seems like the SQL stmt is SO much simpler if I do the join
first.
Apparently /simpler/ means not having to perform a summary query
*and* the join in the same SELECT; i.e. *only* the join? So why not
just encapsulate the summary query in a VIEW, and "do the join" between
the header and the /summary of the/ detail records?:
create view ODtlSum as
( select ordno, dec( sum(DtlQty), 17 ) as SumQty
from ODtl
group by ordno
)
;
select H.ordno, H.TotQty, D.SumQty
from OHdr H
/* left or inner */ join ODtlSum D
using (ordno)
midrange.com
