|
I'm sure. But I'd think this would:
Exec SQL
Select SUM(d.ITNSA),
h.ORDAM
Into :ITNSA,
:ORDAM
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO
Group By h.ONRCU.
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"When a man retires and time is no longer a matter of urgent importance,
his colleagues generally present him with a watch."
-- R.C. Sherriff
There is only 1 header record per ONRCU, as you correctly inferred,but
justusing
adding a Group By at the end, so it looked like this:
Exec SQL
Select SUM(d.ITNSA),
SUM(Distinct(h.ORDAM))
Into :ITNSA,
:ORDAM
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO
Group By h.ONRCU.
did not work.
On Mon, Jun 21, 2010 at 11:38 AM, Dennis Lovelady
<iseries@xxxxxxxxxxxx>wrote:
Actually, the way I interpret the code, there is probably only oneheader
record per order. If that's true, GROUP BY will allow him to dropthe
SUM()But
from the order header record, and the aggregate issue won't apply.
we're left to infer, rather than understand, how the data isorganized.
hasn't
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"When a man says he approves of something in principle, it means he
the slightest intention of putting it into practice."same
-- Prince Otto von Bismark
Using aggregate functions across different join levels of data is
always doing to be a problem...
You could use a Common Table Expression to bring the data to the
SumItmAmtlevel..
with Hdr as (select CUSNR, ROUTE, STOPX, SHPDTISO
SUM(ORDAM) as SumOrdAmt
from ORHDR
where CUSNR = :CUSNR and
ROUTE = :ROUTE and
STOPX = :STOPX and
SHPDTISO = :SHPDTISO)
, dtl as (select CUSNR, ROUTE, STOPX, SHPDTISO
SUM(ITNSA) as SumItmAmt
from ORDTL
where CUSNR = :CUSNR and
ROUTE = :ROUTE and
STOPX = :STOPX and
SHPDTISO = :SHPDTISO)
select SumOrdAmt, SumItmAmt into :ORDAM, :ITNSA
from hdr join dtl using(CUSNR, ROUTE, STOPX, SHPDTISO)
An alternative format that might be easier to understand
with OrderSummary as (select h.ONRCU, h.ORDAM,
sum(d.ITNSA) as
from ORHDR H join ORDTL D
= :SHPDTISO(ONRCU)
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO
agroup by h.onrcu)
select sum(ORDAM), sum(SumItmAmt) into :ORDAM, :ITNSA
from OrderSummary
HTH,
Charles
On Mon, Jun 21, 2010 at 10:32 AM, Jeff Crosby
<jlcrosby@xxxxxxxxxxxxxxxx> wrote:
Been a while since I've asked an SQL question, so I'm due. :)
I will soon, I think, have a need within an RPG program to get
ofcouple of
pieces of summary information from an order header/detail pair
file.files.
One piece from the header file and one piece from the detail
Iright,
so Ican
get the 2 pieces separately like this:
Exec SQL
Select SUM(ORDAM)
Into :ORDAM
From ORHDR
Where CUSNR = :CUSNR and
ROUTE = :ROUTE and
STOPX = :STOPX and
SHPDTISO = :SHPDTISO;
Exec SQL
Select SUM(d.ITNSA)
Into :ITNSA,
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;
I wondered if I could get the 2 pieces with a single statement,
fieldtried
this:
Exec SQL
Select SUM(d.ITNSA),
SUM(h.ORDAM)
Into :ITNSA,
:ORDAM
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;
It executed, but the RPG field ORDAM was waAAAyyy off because
andh.ORDAM
from the header file was summed for each record in the detailfile. I
understand why that is and it makes sense. I did some googling
selectedcame up
with this, using Distinct:
Exec SQL
Select SUM(d.ITNSA),
SUM(Distinct(h.ORDAM))
Into :ITNSA,
:ORDAM
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;
That seemed to me like it would have a problem, because 2
often,order
header records COULD have the same h.ORDAM value. Won't happen
but
it can and does happen sometimes. If I understand Distinct
itopinion
testingwould
only include one of them in the Sum function. So I did some
sideand
found that to be true.
So is there a way to do what I want in one statement? The flip
of that
is, if the statement is very complex, I would rather do it in 2statements
because I believe in KISS.
Thanks.
--
Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com
The opinions expressed are my own and not necessarily the
ofmy
mailingmy
company. Unless I say so.mailing list
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
To post a message email: MIDRANGE-L@xxxxxxxxxxxx--
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing listlist
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com
The opinions expressed are my own and not necessarily the opinion of
company. Unless I say so.mailing
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.