|
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Birgitta Hauser
Sent: Friday, May 19, 2017 9:49 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Variables in ACS Run Sql Scripts.
If you run your SQL script under commitment control, you have to perform a
COMMIT after having created the global variables.
Check where you global variable is generated (you do not specify a schema in
the CREATE statement) and check whether the appropriate schema is within
your library list.
BTW why creating and deleting the global variable each time you execute the
SQL script?
Why not creating the global variable in a schema (like a UDF) and SET it
whenever you need it.
SET YourGlobalVar = 'WHATEVER'
Even though global variables are permanent objects, they work like a data
area in the QTEMP library, i.e. the same variable can be used in different jobs
with different values at the same time without any problems.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Kevin Bucknum
Sent: Freitag, 19. Mai 2017 16:18
To: midrange-l@xxxxxxxxxxxx
Subject: 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;
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-
l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-
l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
