AW: SQL

Hi Rob,

using max(cust#) is not the optimal solution.
It is much easier for the query optimizer to recognize the query if you
add this field in the group by clause.
(Internally the query will be rewritten in this way!)

The more information you can give the query optimizer, the easier it
can determine the optimal access path.

Birgitta

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von rob@xxxxxxxxx
Gesendet: Donnerstag, 31. Marz 2005 23:59
An: RPG programming on the AS400 / iSeries
Betreff: Re: SQL


Tony's suggestion is correct.

One of the examples I sent the other day dealt with another small anomaly. 
 If there is a field that never changes in a grouping but you want it to 
appear in the result set you may have to do a function on it.  For 
example, if you perform

Select order#, cust#, sum(ordamt)
>From OrdDtl
Group by order#

It will blow you smoke because cust# is not a grouped field.  Now, we all 
'know' that cust# is not going to change within a single order#.  However 
we still have to work around this by doing something like:

Select order#, max(cust#), sum(ordamt)
>From OrdDtl
Group by order#


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Tony Carolla <carolla@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/31/2005 04:43 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL






Try this:

select ilnsoldto, sum(ilneprice) from impiln00 group By ilnsoldto


On Thu, 31 Mar 2005 13:40:20 -0800, Douglas W. Palme
<dpalme@xxxxxxxxxxxxxxxxxxxxx> wrote:
> As a follow up to the questions I had the other day, I've ordered the
> appropriate license key for the SQL and have started working on learning
> embedded sql to use with my rpg.
> 
> Needless to say, it'll delay some reports a bit but I think in the long 
run
> it'll help.
> 
> One question I do have, I am running the sql interactive query and I am 
just
> playing with it a bit, but have run into a problem that maybe one of you 
can
> help me out with; considering the slightly different syntax between sql 
for
> the iSeries and what I am used to.
> 
> I have a PF that cotnains among other fields, ilneprice (which contains
> extended price amount), and a ilnsoldto field which is a reference field 
for
> the customer number.
> 
> When I run 'select ilnsoldto' by itself it runs fine, when I run sum
> (ilneprice) by itself its fine but when I run the following command it 
tells
> me the ilnsoldto field is incorrect:
> 
> 
> 
> Suggestions? Comments?
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing 
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
> 


-- 
"Enter any 11-digit prime number to continue..."
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.