AW: SQL Sum(Distinct())

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi Jeff,

I'm not sure about what's really happening when the query is executed, but I
assume the query engines are smart enough to first execute the order header
file and select the orders according to the where conditions and join the
result (i.e. selected order no) with the order detail and only group those
records.

You may get more information when running the query through visual explain.

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 Jeff Crosby
Gesendet: Monday, 21. June 2010 22:45
An: Midrange Systems Technical Discussion
Betreff: Re: SQL Sum(Distinct())

Birgitta,

Question on this SQL you posted for me today:

         Exec SQL
           Select Sum(d.ITNSA),
                  Sum(h.ORDAM)
             Into :ActItmSlsAmt,
                  :OrdActOrdAmt
             From ORHDR h Join (Select ONRCU,
                                       Sum(ITNSA) As ITNSA
                                  From ORDTL
                                  Group By ONRCU)
             d On h.ONRCU = d.ONRCU
             Where h.CUSNR = :CUSNR and
                   h.ROUTE = :ROUTE and
                   h.STOPX = :STOPX and
                   h.SHPDTISO = :SHPDTISO;

The inner Select doesn't have a Where clause.  Does this mean that every
time this statement is executed, it will create a temporary file of /*all*/
the records on ORDTL?  Or is SQL smart enough to not do that?

Thanks.


On Mon, Jun 21, 2010 at 11:11 AM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx>wrote:

> Hi,
>
> you may try something like this:
>
> 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 Jeff Crosby
> Gesendet: Monday, 21. June 2010 16:33
> An: Midrange Systems Technical Discussion
> Betreff: SQL Sum(Distinct())
>
> 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.