× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Correct. That's why we also calculate on the 10#.


On Mon, Jun 21, 2010 at 3:57 PM, Charles Wilt <charles.wilt@xxxxxxxxx>wrote:

Jeff,

I don't know about your customers, but the ones I've dealt with
wouldn't like getting a delivery fee because the 10# case they ordered
only contained 8#... :)

Especially given that the case pulled isn't under their control.

Just a thought...

Charles



On Mon, Jun 21, 2010 at 3:23 PM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:
Good catch, but no.

Some items are random weight and sold by the pound. The actual weight is
not known until the product is physically pulled in the warehouse.

So, as an example, an item might be "10#RW" meaning 10# avg case. ORDAM
would have been calculated with 10.00#, but the actual case pulled might
be
as much as 13.50# (or as low as 8.00#). So both checks need to be made
even
if sum(ITNSA) might be larger less than 1% of the time.

Sometimes it seems like 90% of program coding is done to handle 10% of
the
possibilities.



On Mon, Jun 21, 2010 at 3:01 PM, Charles Wilt <charles.wilt@xxxxxxxxx
wrote:

Jeff,

Reading your description, it would seem that sum(ORDAM) would always
be greater than or equal to sum(ITNSA)...

Thus, isn't it sufficient to only check sum(ORDAM)?

Charles

On Mon, Jun 21, 2010 at 2:02 PM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx

wrote:
Dennis,

I didn't go into too great of details.

We're a food distributor, delivering to, primarily, restaurants. Some
restaurants want multiple invoices. For example, food items on 1
invoice
and non-food items on another invoice.

We're making some changes as regards minimum delivery in order to drop
the
product without a service fee. The minimum is per *drop*, not
invoice.
A
'drop' is all invoices with the same customer/route/stop/ship date.

With multiple invoices, each invoice in and of itself might be below
the
minimum, but put together exceed the minimum for that drop. In that
case,
there is no drop fee.

We look at 2 figures to determine whether the drop is above the
minimum:
1)
the actual item sales, (which is Sum(d.ITNSA)), and 2) what the total
drop
would have been if everything shipped as ordered (which is
Sum(h.ORDAM)).
If either one is over the minimum, we won't charge the drop fee.

Let's say the minimum drop is $500. The actual sales for the drop
came
in
at $497.55. *But*, we were short a case of, say, oranges, with a
price
of
$25 for the case. In this case (no pun intended), we will *not*
charge a
drop fee because it's *our* fault we don't have the oranges, not the
customer's.



On Mon, Jun 21, 2010 at 1:31 PM, Dennis Lovelady <
iseries@xxxxxxxxxxxx
wrote:

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.


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




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





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.