RE: Variables in ACS Run Sql Scripts. -- MIDRANGE-L

The command they use is actually a front end to a QMQRY that I wrote a
long time ago. Right now this is the only thing this particular user has
to sign on to the green screen for. The query gets run, a file is
created, they pull it into excel, and depending on who they are giving
the data to, they delete some of the rows. If they remember, they are
supposed to go delete the file that was created. If they forget, I have
a routine that finds the files and deletes them every month. The idea is
to have all the different versions of these queries saved, so that the
user can just open one up, change the parameters at the top of the file,
run it, and then save it as an excel spreadsheet.

Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Mark S Waterbury
Sent: Friday, May 19, 2017 9:37 AM
To: Midrange Systems Technical Discussion
Subject: Re: Variables in ACS Run Sql Scripts.

Kevin,

IMHO, "Run SQL Scripts" is not really an "end user" tool...

What "command(s)" are they using now to "create the file"...?

Create a *QMQRY and show them how to run it using STRQMQRY --
*QMQRYs have the ability to have "substitution values" and you can

easily

pass whatever values you want into the QM/Query, when you run it via

the

STRQMQRY command.

Prompt the STRQMQRY command and press F10=Aditional parameters, and
page down until you see the "Set variables" parameter (you have to

page

down to see it).

You could even create a nice "front-end" to provide a "form" for the

user to

fill-in-the-blanks with the required substitution values and then run

the

STRQMQRY command for the user (you could do this in a browser or using
VB.NET or Java on a PC, for a modern-looking GUI form.)

QueryManager/400 (QM/400) comes bundled with IBM i, at no extra

charge,

and uses "real" SQL so you benefit from the SQE optimizer, etc.

Hope that helps...

Mark S. Waterbury

> On 5/19/2017 10:17 AM, Kevin Bucknum wrote:

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

least easier than the command is. I'm trying to set some variables

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

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,

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

INS_RSP,

HCCLNO AS CLAIM,

HRCOCD AS CC,

COALESCE((SELECT
CCINAG FROM MCOL WHERE CCCOCD

= HRCOCD), ' ')

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

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