Variables in ACS Run Sql Scripts.

I'm supposed to show someone how to use Run Sql Scripts this afternoon
to replace a process where they run a command that creates a file and
they then pull that into excel. I can run the query without any issues
if I put the date range in the where clause, but I want this to be at
least easier than the command is. I'm trying to set some variables at
the top of the script so that they don't have to go down and change the
values in the where clause every time. We are at V7R1 TR11. So far
nothing I've tried works, and I can't find any good examples of how to
do this anywhere. If I just declare and set the variable in the script,
when it gets down to the where cause it says that it can't find it. If I
put a begin/end around the script, it gives me invalid token errors. My
google-fu is failing me today and I can't seem to find what I'm missing.




Ugly 10 year old sql script follows. If I wrap it with a begin/end, I
get an invalid token ':' error.



-- CHGAPPFILE by period.

SET SCHEMA = mpms04;

SET PATH = *LIBL;



CREATE OR REPLACE VARIABLE @STARTPERIOD INTEGER DEFAULT 201701;

CREATE OR REPLACE VARIABLE @ENDPERIOD INTEGER DEFAULT 201702;



SELECT HCPTNO AS ACCOUNT,

PTCHRT AS CHART,

TRIM(PTPLN) CONCAT ', ' CONCAT TRIM(PTPFN) AS NAME,

COALESCE(PPPADR, PTGADR) AS ADDRESS,

COALESCE(PPPCTY, PTGCTY) AS CITY,

COALESCE(PPPST, PTGST) AS STATE,

SUBSTR(CHAR(COALESCE(PPPZIP, PTGZIP)), 1, 5) AS
ZIP_CODE,

CASE

WHEN PTPDOB = 0

THEN '00/00/0000'

ELSE SUBSTR(CHAR(PTPDOB), 5, 2) CONCAT '/'
CONCAT SUBSTR(CHAR(PTPDOB),

7, 2) CONCAT '/' CONCAT
SUBSTR(CHAR(PTPDOB), 1, 4)

END AS DOB,

PTPSEX AS SEX,

PTPRACE AS RACE,

COALESCE((SELECT pipol FROM mpin WHERE piptno = ptptno
AND pips = 'P' AND pidlcd

= ' '), ' ') AS POLICY,

COALESCE((SELECT pigr FROM mpin WHERE
piptno = ptptno AND pips

= 'P' AND pidlcd = ' '), ' ') AS
GROUP#,

SUBSTR(CHAR(HRTRDT),
5, 2) CONCAT '/' CONCAT

SUBSTR(CHAR(HRTRDT),
7, 2) CONCAT '/' CONCAT

SUBSTR(CHAR(HRTRDT),
1, 4) AS DOS,

SUBSTR(CHAR(HRPER), 5,
2) CONCAT '/' CONCAT

SUBSTR(CHAR(HRPER), 1,
4) AS PROC_PER,

HRDRCD AS PHY,

HRASCD AS ASST,

HCRFCD AS REFERRAL,

FCGRPR AS FG,

HRFNCL AS FC,

HRLOC AS LOC,

HRPOS AS POS,

TMRVCD AS REV,

HRTKNO AS TICKET,

HRTRCD AS TRANS,

HRTRDS AS DESCRIPTION,

HRCPT AS CPT,

HRMOD1 AS MOD1,

HRMOD2 AS MOD2,

HRMOD3 AS MOD3,

HRMOD4 AS MOD4,

CASE

WHEN HRTRAM >= 0

THEN HRUNIT

ELSE HRUNIT * -1

END AS UNITS,

HRTRAM AS AMOUNT,

COALESCE((SELECT
SUM(HEAPAM) FROM MHEX WHERE

HEPTNO = HCPTNO AND
HECHDT = HCTRDT AND HECHSQ

= HCTRSQ AND HETRTY =
'P'), 0) AS PMT,

COALESCE((SELECT
SUM(HEAPAM) FROM MHEX WHERE

HEPTNO = HCPTNO AND
HECHDT = HCTRDT AND HECHSQ

= HCTRSQ AND HETRTY =
'P' AND HEINPY = 'Y'), 0)

AS PMT_INS,

COALESCE((SELECT
SUM(HEAPAM) FROM MHEX WHERE

HEPTNO = HCPTNO AND
HECHDT = HCTRDT AND HECHSQ

= HCTRSQ AND HETRTY =
'P' AND HEINPY <> 'Y'),

0) AS PMT_OTH,

COALESCE((SELECT
SUM(HEAPAM) * -1 FROM MHEX

WHERE HEPTNO = HCPTNO
AND HECHDT = HCTRDT AND

HECHSQ = HCTRSQ AND
HETRTY = 'A'), 0) AS ADJ,

HRTRBL AS BAL,

HCPTAM AS PAT_RSP,

HCPRAM + HCSEAM AS
INS_RSP,

HCCLNO AS CLAIM,

HRCOCD AS CC,

COALESCE((SELECT
CCINAG FROM MCOL WHERE CCCOCD

= HRCOCD), ' ') AS
CC_TYPE,

CASE

WHEN HRTRAM >= 0

THEN HRanun

ELSE HRanun * -1

END AS ANS_UNITS,

hrconc AS
CONCURRENCIES,

SUBSTR(DIGITS(hctmfm),
1, 2) concat ':' concat

SUBSTR(DIGITS(hctmfm),
3, 2) AS TIME_FROM,

SUBSTR(DIGITS(hctmto),
1, 2) concat ':' concat

SUBSTR(DIGITS(hctmto),
3, 2) AS TIME_TO,

CASE

WHEN hctmfm <> 0 OR

hctmto <> 0

THEN TIMESTAMPDIFF(4,


CHAR(TIMESTAMP(SUBSTR(DIGITS(hrto), 1,

4) concat '-'
concat


SUBSTR(DIGITS(hrto), 5, 2) concat '-'

concat
SUBSTR(DIGITS(hrto), 7, 2)

concat '-'
concat


SUBSTR(DIGITS(hctmto), 1, 2) concat

'.' concat
SUBSTR(DIGITS(hctmto), 3,

2) concat
'.00.000000') -


TIMESTAMP(SUBSTR(DIGITS(hrfrm), 1, 4)

concat '-'
concat


SUBSTR(DIGITS(hrfrm), 5, 2) concat '-'

concat
SUBSTR(DIGITS(hrfrm), 7, 2)

concat '-'
concat


SUBSTR(DIGITS(hctmfm), 1, 2) concat

'.' concat
SUBSTR(DIGITS(hctmfm), 3,

2) concat
'.00.000000')))

ELSE 0

END AS TIME_DIFF,

CASE

WHEN (

SELECT COUNT(*)

FROM mhtr r,

mhex

WHERE heptno = hcptno
AND

hechdt = hctrdt AND

hechsq = hctrsq AND

r.hrptno = heptno AND

hetrdt = r.hrtrdt AND

hetrsq = r.hrtrsq AND

r.hrtrcd = 'RVSCH')

> 0

THEN 'R'

ELSE ' '

END AS RVSCH,

HRBTUS AS Batch_Name,

SUBSTR(CHAR(HRBTDT),
5, 2) CONCAT '/' CONCAT

SUBSTR(CHAR(HRBTDT),
7, 2) CONCAT '/' CONCAT

SUBSTR(CHAR(HRBTDT),
1, 4) AS Batch_Date,

HRBTSQ AS Batch_Seq,

P_CALCRVU(HRPTNO,

HRTRDT,

HRTRSQ) AS Ext_RVUS,

COALESCE((SELECT dgicd
FROM mdia WHERE hrdg1 =

dgdiag AND hrcdst =
dgcdst), hrdg1) AS DG1,

COALESCE((SELECT dgicd
FROM mdia WHERE hrdg2 =

dgdiag AND hrcdst =
dgcdst), hrdg2) AS DG2,

COALESCE((SELECT dgicd
FROM mdia WHERE hrdg3 =

dgdiag AND hrcdst =
dgcdst), hrdg3) AS DG3,

COALESCE((SELECT dgicd
FROM mdia WHERE hrdg4 =

dgdiag AND hrcdst =
dgcdst), hrdg4) AS DG4,

HCID AS Special_Id

FROM MHTC,

MHTR,

MPAT LEFT

JOIN MPAP ON PTPTNO = PPPTNO,

MFCM,

MTRM

WHERE HRPTNO = HCPTNO AND

HRTRDT = HCTRDT AND

HRTRSQ = HCTRSQ AND

HRPTNO = PTPTNO AND

HRDLCD = ' ' AND

FCFCCD = HRFNCL AND

TMTRCD = HRTRCD AND

TMTRTY = 'C' AND

HRPER >= @STARTPERIOD AND

HRPER <= @ENDPERIOD;



DROP VARIABLE startPeriod;

DROP VARIABLE endPeriod;