Re: SQL Sum(Distinct())

[Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>]

Heh.  I'm not sure what I'm looking for here.

In the graph,

ORHDR has an index probe and a table probe
ORDTL has an index probe, table probe, and "aggregation"

The "aggregation" and ORHDR table probe then converge to "nested loop join"
which then proceeds to another aggregation, then final select (with a
checkered flag - I like).

The details on that first *aggregation* (from ORDTL) says:

Estimated rows selected and query join info
Rows Selected Per Plan Step Iteration 1
Rows Processed During Last Plan Step 1
Plan Step Iterations 2
Total Rows Selected 2
Total Rows Processed 2
Optimize for N Rows All
Percent Selectivity 100
Cumulative Percent Selectivity 100
Fetch N Rows All

The *table probe* of ORDTL (the inner Select) says:

Estimated rows selected and query join info
Rows Selected Per Plan Step Iteration 10.117
Rows Processed During Last Plan Step 951
Plan Step Iterations 2
Total Rows Selected 20.234
Total Rows Processed 1,902
Optimize for N Rows All
Percent Selectivity 100
Cumulative Percent Selectivity 1.063
Fetch N Rows All


Does that mean the table probe selected 20.234 rows?  And just how do you
select ".234" rows?  :)



On Tue, Jun 22, 2010 at 8:52 AM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>wrote:

> Visual Explain did advise me to build an index on ORHDR of SHPDTISO, ROUTE,
> STOPX, CUSNR, ONRCU which makes sense.
>
> It also suggests I build an EVI on ORDTL:
>
> /* Creating index ORDTLTEST
> When creating this index the database connection should have a sort
> sequence of *HEX. */
> CREATE ENCODED VECTOR INDEX ORDTLTEST ON ORDTL (ONRCU  ASC, ITNSA  ASC)
> WITH 65537 DISTINCT VALUES;
>
> Never done an EVI before.  I always thought they were more for large
> files.  This one will usually have <5000 records.
>
>
> On Tue, Jun 22, 2010 at 12:47 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:
>
> > 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.
> >
> >
> > --
> > 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.