RE: SQL Sum(Distinct())

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

Have a look at GROUP BY.  If I understand your issue (I may not), that
should resolve it.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"A conference is a gathering of important people who singly can do nothing
but together can decide that nothing can be done."
        -- Fred Allen 



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