Re: SQL

[rob@xxxxxxxxx]

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.