RE: dynamically store a rolling six years of data in a tableandhavemeaningful column field names and descriptions thatcanchangeyear-to-year?

Don't forget you can key the source (we use a source file labeled
QSQLSRC) and use the RUNSQLCMD to create the table. That allows you to
quickly make modifications and alternate views.
The other nicety is the file is dynamic. If you base it on some key
file that holds period definitions then when the periods change the file
contents changes.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
elehti@xxxxxxxxxxxxxxxxxx
Sent: Tuesday, November 01, 2011 3:59 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: dynamically store a rolling six years of data in a
tableandhavemeaningful column field names and descriptions
thatcanchangeyear-to-year?

Bill,
Your recommendation to do an SQL Select with Case statements is great
and I thank you.

Your observation made me give myself a dope slap because I had forgotten
that I have previously used STRQMQRY with output parms to create files
with descriptive column names, shown here:

STRQMQRY QMQRY(F55PROFE) OUTPUT(*OUTFILE) +
OUTFILE(PLAN/F55PROFE) SETVAR((BEGIN +
&PSOPT1) (END &PSOPT2))

The QMQRY logic is:
SELECT
TDAC05,TDELNM,SDADDJ,SDDOCO,SDDCTO,SDAN8,TDMNNS,TDCTYS,TDADDO,TDADZO,

SUM(CASE WHEN SDSRP1='HDW' THEN SDAEXP * 0.01 ELSE 0 END)

AS WoodGateHdw,

SUM(CASE WHEN SDSRP1='HDW' THEN TD$PXP ELSE 0 END)

AS WoodGateHdw_Profit,

SUM(CASE WHEN SDSRP1='QR ' THEN SDAEXP * 0.01 ELSE 0 END)

AS QuickRok,

{ and many more SUM AS statements omitted here}

FROM F42119J9 WHERE SDADDJ BETWEEN &BEGIN AND &END

AND SDKTLN=0

AND SDDCTO IN('SO', 'SC','SP')

AND SDLNTY IN('P','S','C')

AND SDMCU NOT IN(' 350',' 2000')

AND SDTORG NOT IN('LMITCHELL', 'JMCINTYRE' )

GROUP BY TDAC05,TDELNM,SDADDJ,SDDOCO,SDDCTO,SDADDJ,SDDOCO,SDDCTO,SDAN8,

TDMNNS,TDCTYS,TDADDO,TDADZO

ORDER BY TDAC05,TDELNM,SDADDJ,SDDOCO,SDDCTO,SDADDJ,SDDOCO,SDDCTO,SDAN8,

TDMNNS,TDCTYS,TDADDO,TDADZO