Thank you so much - you went above and beyond with this one.
This is like a semester of SQL - all in one email.
Its going to take me some time to play with but I think it is what I am looking for.
I don't think the join approach works.
The files I am using are harder to explain so I used order hdr & dtl.
The real files are 4 levels: Contract, contract-terms, contract/shipment, and shipment.
The problem seems to be that for contract 123, there can be two terms 123A and 123B. For each of those there can be multiple shipments. When I join them all together, the amounts in the top levels are duplicated. When I add the joined rows together, the amounts in the TERMS level are doubled, tripped and more because of the 1-to-many relationships.
I was hoping for a simple solution but from the varied responses and especially yours, its not as simple as I had hoped - but still doable.
I was wondering why all of the SQL/400 books I have avoided these types of queries. I have two SQL books. One has only a paragraph on CTEs and the other only a page.
I think one could write an entire book on CTEs and where to use them.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, April 03, 2013 6:57 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL: how to join and roll up THREE levels
On 03 Apr 2013 12:15, Stone, Joel wrote:
<<SNIP>>
Now I have to roll up THREE levels; Hdr, DTL, and sub-DTL.
I thought that I could use the join ideas presented here, but it
seems that the SUM() functions duplicate rows in three levels and
the sums are multiples of what is required.
Can any of the examples using TWO levels (HDR & DTL) be expounded
upon to add a third level?
Or is there a better way to make this happen?
I like things simple - is there a way to accomplish this in steps
so it is simple and I can understand what I did a month from now?
Given the Hdr file is defined as:
create table OHdr
( ordno dec(7), primary key (ordno), TotQty dec(17)
)
;
Does the DDL for the other two files look something like?:
create table ODtl
( ordno dec(7)
, DtlQty dec(13)
, foreign key (ordno) references OHdr (ordno)
on delete cascade on update restrict
) /* and SUM(DtlQty) should match the TotQty in Hdr file */
;
create table OSub
( ordno dec(7)
, SubQty dec(13)
, foreign key (ordno) references OHdr (ordno)
on delete cascade on update restrict
) /* and SUM(SubQty) should match the TotQty in Hdr file */
;
If so [noting the assumption in the comment that the summed values of
SubQty should equate with TotQty, just as the summed values of DtlQty
should equate with TotQty], then...
I would probably choose to run the join query of the two files [Hdr
and Dtl files], and then repeat that query, but using the sub-Dtl file
in place of the Dtl file. Comparing the TotQty column value to just one
other column is much clearer than comparing to two other columns, esp.
because there could be at least one correctly matching. Regardless, ...
IMO the *simplest* means which would have the future readers of the
SQL being the least confused, is to encapsulate each of the summary
queries in their own VIEW, *then* join the header file with the summary
VIEWs in a SELECT. IMO this is also easier for the initial creation,
because the steps are incremental work and each step is straightforward,
and allows the final query request to be very succinct:
create view ODtlSum as
( select ordno, dec( sum(DtlQty), 17 ) as SumDtlQty
from ODtl
group by ordno
) /* One row per ordno gives one-to-one or one-to-zero */
;
create view OSubSum as
( select ordno, dec( sum(SubQty), 17 ) as SumSubQty
from ODtl
group by ordno
) /* One row per ordno gives one-to-one or one-to-zero */
;
select H.ordno, H.TotQty, D.SumDtlQty, S.SumSubQty
from OHdr H
/* left or inner */ join ODtlSum D
using (ordno)
/* left or inner */ join OSubSum S
using (ordno)
where H.TotQty <> D.SumDtlQty
or H.TotQty <> S.SumSubQty
After the CREATE VIEW activity is completed as setup activity, the
eventual SELECT that is coded is very simple and is IMO also very clear.
The definitions of the VIEWs are easily reviewed from the catalog,
DSPFD, or other retrieval [e.g. by the retrieve SQL DDL API or iNav
using that API].
Avoiding the separate VIEW objects to manage, the SELECT can get data
from the summary results [that like the VIEWs also give one-to-one or
one-to-zero for ordno when joined with Hdr] in a derived-table or
subquery, and the join performed with the header to those temporary
results; e.g. perhaps one of:
/* join using a NTE for each summary query */
select H.ordno, H.TotQty, D.SumDtlQty, S.SumSubQty
from OHdr H
/* left or inner */ join
( select ordno, dec( sum(DtlQty), 17 ) as SumDtlQty
from ODtl
group by ordno ) D
on H.ordno = D.ordno
/* left or inner */ join
( select ordno, dec( sum(SubQty), 17 ) as SumSubQty
from OSub
group by ordno ) S
on D.ordno = S.ordno
where H.TotQty <> D.SumDtlQty
or H.TotQty <> S.SumSubQty
/* above is easily modified to use a CTE vs NTE for agggregates */
with
SumDtl as
( select ordno, dec( sum(DtlQty), 17 ) as SumDtlQty
from ODtl
group by ordno
)
, SumSub as
( select ordno, dec( sum(SubQty), 17 ) as SumSubQty
from OSub
group by ordno
)
select H.ordno, H.TotQty, D.SumDtlQty, S.SumSubQty
from OHdr H
/* left or inner */ join
SumDtl D
on H.ordno = D.ordno
/* left or inner */ join
SumSub S
on D.ordno = S.ordno
where H.TotQty <> D.SumDtlQty
or H.TotQty <> S.SumSubQty
/* an implied join using correlated scalar subselects */
select H.ordno, H.TotQty
, ( select dec( sum(DtlQty), 17 )
from ODtl D
where D.ordno = H.ordno
) as SumDtlQty
, ( select dec( sum(SubQty), 17 )
from OSub S
where S.ordno = H.ordno
) as SumSubQty
from OHdr H
where H.TotQty <> ( select dec( sum(DtlQty), 17 )
from ODtl D
where D.ordno = H.ordno )
or H.TotQty <> ( select dec( sum(SubQty), 17 )
from OSub S
where S.ordno = H.ordno )
The simple grouping query with the SUM aggregate using HAVING
selection for selection, a query that was offered as a resolution in the
prior message thread [subject: SQL: how to join header and detail
records] /breaks/ when trying to add another file to the JOIN because
the number of rows multiplies. A summation of the values for each
joined table is off by a multiple of the counts of rows added by each
additional joined table. While there are presumably ways to accomplish
such a query, they would be ugly. For example the following [albeit
this fails due to sql0112 on v5r3, which may or may not be a proper
error denoting a restriction which precludes that attempt]:
select h.ordno, h.totqty
, dec( dec(sum(dtlqty), 17)
/ (select int(count(*))
from osub s
where s.ordno = h.ordno )
, 17
) as dtlsum
, dec( dec(sum(subqty), 17)
/ (select int(count(*))
from odtl d
where d.ordno = h.ordno )
, 17
) as subsum
from ohdr h
join odtl d
on h.ordno = d.ordno
join osub s
on d.ordno = s.ordno
group by h.ordno , h.totqty
having h.totqty <> dec( dec(sum(dtlqty), 17)
/ (select int(count(*))
from osub s
where s.ordno = h.ordno )
, 17)
or h.totqty <> dec( dec(sum(subqty), 17)
/ (select int(count(*))
from odtl d
where d.ordno = h.ordno )
, 17)
midrange.com
