RE: RPG/SQL

I'd use a case statement for example
Select
Sum(case when year = prior year then sales else 0 end) as Prior_Year_sls,
Sum(case when year = current Year then sales else 0 end) as Current_year_sls

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

Chuck

I think there are a couple typos in my reply - where I refer to tysales and lysales in the final SELECT. Check it out.

Vern

On 6/27/2012 12:00 PM, Graves, Chuck 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.