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


  1.  Home
  2. MIDRANGE-L
  3. June 2010

Re: SQL Sum(Distinct())

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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.