RE: SQL Sum(Distinct()) -- MIDRANGE-L

Ok. I guess I missed where he explained about the content, what the columns
were, and how he was using the data. Thanks.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The desire of appearing clever often prevents our becoming so."
-- La Rochefoucauld

You'll get several records, because you may have several orders per
customer
and/or route and/or stopx and/or shpdtiso.
That means you need to summarize the order details per order (1 row per
order) and join the result with the order header and summarize the
results
of the join.
Just like I've done it in my example:

Exec SQL
Select Sum(D.ITNSA), Sum(H.ORDAM)
Into :Fld1, :Fld2
From OrdHdr h join (Select Oncru, sum(ITNSA) ITNSA
From OrdDtl
Group By ONCRU) d
On h.Oncru = d.Oncru
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;

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 Dennis Lovelady
Gesendet: Monday, 21. June 2010 18:24
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL Sum(Distinct())

Oh. Sorry. I made the same mistake as Charles. Needed to include
ORDAM in
the GROUP BY.

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
--
"...Yes, the lectures are optional. Graduation is also optional."
-- Professor Brian Quinn

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

just
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

one

header

record per order. If that's true, GROUP BY will allow him to

drop

the

SUM()
from the order header record, and the aggregate issue won't apply.

But

we're left to infer, rather than understand, how the data is

organized.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"When a man says he approves of something in principle, it means

he

hasn't

the slightest intention of putting it into practice."
-- 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

same

level..

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

SumItmAmt

from ORHDR H join ORDTL D

using

(ONRCU)
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE

and

h.STOPX = :STOPX

and

h.SHPDTISO

= :SHPDTISO

group 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

a

couple of

pieces of summary information from an order header/detail

pair

of

files.

One piece from the header file and one piece from the detail

file.

I

can

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,

so I

tried

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

field

h.ORDAM

from the header file was summed for each record in the detail

file. I

understand why that is and it makes sense. I did some

googling

and

came 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

selected

order

header records COULD have the same h.ORDAM value. Won't

happen

often,

but

it can and does happen sometimes. If I understand Distinct

right,

it

would

only include one of them in the Sum function. So I did some

testing

and

found that to be true.

So is there a way to do what I want in one statement? The

flip

side

of that

is, if the statement is very complex, I would rather do it in

2

statements

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

opinion

of

my

company. Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing 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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing 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.

--
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

my

company. Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.