Re: RPG/SQL

Chuck, this is most excellent. I'm especially happy to see the use of host variables in the conditions of the CASE construct.

Thanks for showing this - so much better than a general announcement that the problem got solved!!

Vern

On 6/28/2012 5:07 PM, Graves, Chuck wrote:

Thanks to all for the tips. Thanks to the sample provided by Bill Erhardt, I was able to product this:

exec SQL
declare stor_sls cursor for
select sdan8,(sum(case when sdfy=:last_yr then sdaexp/100 else 0
end)) as ly$, (sum(case when sdfy=:curr_yr then sdaexp/100
else 0 end)) as ty$ from f4211l9 where sdivd>=:j_startp and
sdivd<=:j_endp and sdmcu=:mcu and sdlnty not
in('T ', 'TN','% ') or sdivd>=:j_start and sdivd<=:j_end and
sdmcu=:mcu and sdlnty not in ('T ','TN','% ')
group by sdan8 order by sum(sdaexp) desc;

Which gave me what I needed...

Thanks Bill and everyone else

Chuck

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Graves, Chuck
Sent: Wednesday, June 27, 2012 10:47 AM
To: RPG programming on the IBM i / System i
Subject: RE: RPG/SQL

FYI, these are JDEdwards files, so the dates are in century julian (cyyddd)

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Graves, Chuck
Sent: Wednesday, June 27, 2012 10:45 AM
To: RPG programming on the IBM i / System i
Subject: RE: RPG/SQL

Following the select, I just zip through the data set and produce a report which is just:

Customer# Sales$

What I need is:

Customer# This Year Sales$ Last Year Sales$

Either one of which(this year/last year) could be *ZERO



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Wednesday, June 27, 2012 10:41 AM
To: RPG programming on the IBM i / System i
Subject: Re: RPG/SQL

Hi Alan

I think he wants both year's total in one record - sort of a pivot kind of thing, right?

Vern

On 6/27/2012 12:10 PM, Alan Campin wrote:

My guess would be

Exec SQL
declare Stor_sls Cursor for
Select Year(sdivd),
sdan8,
Sum(sdaexp/100)
from f421119
where sdivd>=:j_start and
sdivd<=:j_end and
sdlnty not in('T ', 'TN', '% ') and
sdmcu=:mcu
group by Year(iDate(sdivd)),
sdan8
order by 3 desc;

Only issue here if the sdivd field is true date field. Assuming that
sdivd is not a true date field but a numeric field you are going to
need to get into a date field. With iDate it would be something this.

Exec SQL
declare Stor_sls Cursor for
Select Year(iDate(sdivd)),
sdan8,
Sum(sdaexp/100)
from f421119
where sdivd>=:j_start and
sdivd<=:j_end and
sdlnty not in('T ', 'TN', '% ') and
sdmcu=:mcu
group by Year(iDate(sdivd)),
sdan8
order by 3 desc;


On Wed, Jun 27, 2012 at 11:00 AM, Graves, Chuck <cgraves@xxxxxxxxxxxxxx> wrote:

What type of select statement do I need to produce a data set containing:

Customer#
2011 sales
2012 sales

Generated from a file containing sales from several years.

I am using this statement:

exec SQL
declare stor_sls cursor for
select sdan8,
sum(sdaexp/100)
from f4211l9
where sdivd>=:j_start and sdivd<=:j_end and
sdlnty not in('T ', 'TN', '% ') and
sdmcu=:mcu group by sdan8
order by sum(sdaexp) desc;

This statement produces what I want for the given date range..i.e.

Customer#
2012 sales


but I need a separate field for the prior years sales

Do I declare a separate cursor for the prior year and then join the two???

Thanks in advance...
Chuck






[Rodda Paint Turns 80!] Chuck Graves
Director of Information Systems
Rodda Paint Co.<http://www.roddapaint.com>
6107 N. Marine Drive
Portland, Oregon 97203
(503) 737-6042

--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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.