Re: RPG/SQL

Chuck

I'd use correlated table expressions - that gives you a couple "tables" to join - the join field would be the customer#

Your SELECT would have something like this - I don't know all the details of your table, but I think a variation of what you have should work.

Here goes -

with lastyear as

(select sdan8,
sum(sdaexp/100) lysales
from f4211l9
where sdivd>=:ly_start and sdivd<=:ly_end and
sdlnty not in('T ', 'TN', '% ') and
sdmcu=:mcu group by sdan8),
thisyear as
(
select sdan8,
sum(sdaexp/100)
from f4211l9 tysales
where sdivd>=:ty_start and sdivd<=:ty_end and
sdlnty not in('T ', 'TN', '% ') and
sdmcu=:mcu group by sdan8)
select lastyear.sdan8,lastyear.tysales,thisyear.lysales from lastyear join thisyear
on lastyear.sdan8 = thisyear.sdan8
order by tysales desc

I think this is right - there might be a way to use CASE - but the recent discussions of host variables in CASE might preclude this.

Notice that this takes 2 more host variables for the separate years - maybe it can be done using just 2 and subtracting 1 for the previous year in each CTE.

Although I wonder about preparing a statement with CASE and host-variable markers, then DECLARE CURSOR from the prepared statement. Not sure at all about that.

HTH
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
;

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