It is possible to pass the SQL result from the variable &PRD into :
CHGVAR VAR(&DOCNAME) VALUE('/Archive/Folder/FileTransaction_' || &PRD || '.csv')
The SQL query produces a date value but I am getting the file result concatonated with SQL statement instead of the statement result.
The result should produce "/Archive/Folder/FileTransaction_201808.csv" but I getting the SQL statement concatonated instead.
DCL VAR(&SQL) TYPE(*CHAR) LEN(182)
DCL VAR(&DSQL) TYPE(*CHAR) LEN(22)
DCL VAR(&PRD) TYPE(*CHAR) LEN(70)
DCL VAR(&DPRD) TYPE(*CHAR) LEN(20)
DCL VAR(&DOCNAME) TYPE(*CHAR) LEN(45)
CHGVAR VAR(&DSQL) VALUE('DROP VIEW QTEMP/CASH1')
CHGVAR VAR(&DPRD) VALUE('DROP VIEW QTEMP/PRD')
CHGVAR VAR(&SQL) VALUE('CREATE VIEW QTEMP/CASH1 AS SELECT PORT_CD, PORT_NAME, SEC_CD, +
SEC_NAME, DIV_CPS, QTY, DIV_PRC, SEC_CCY, EX_RATE, VALUE_DT, AMOUNT_SC, +
AMOUNT_LC, EX_DATE FROM TESTLIB/OPTR_CASH')
CHGVAR VAR(&PRD) VALUE('CREATE VIEW QTEMP/PRD AS SELECT DISTINCT PRD FROM +
TESTLIB/OPTR_CASH')
/* Run SQL command to create qtemp view */
RUNSQL SQL(&DSQL) COMMIT(*NONE)
MONMSG MSGID(SQL0000)
RUNSQL SQL(&DPRD) COMMIT(*NONE)
MONMSG MSGID(SQL0000)
RUNSQL SQL(&PRD) COMMIT(*NONE)
MONMSG MSGID(SQL9999) EXEC(GOTO CMDLBL(EMAIL))
RUNSQL SQL(&SQL) COMMIT(*NONE)
MONMSG MSGID(SQL9999) EXEC(GOTO CMDLBL(EMAIL))
/* Copy record from created view into csv file */
CHGVAR VAR(&DOCNAME) VALUE('/Archive/Folder/FileTransaction_' || &PRD || '.csv')
CPYTOIMPF FROMFILE(QTEMP/CASH1) TOSTMF(&DOCNAME) MBROPT(*REPLACE) STMFCCSID(*PCASCII) +
RCDDLM(*CRLF) DTAFMT(*DLM) FLDDLM(',') ADDCOLNAM(*SQL)
MONMSG MSGID(CPF9999) EXEC(GOTO CMDLBL(EMAIL))
GOTO CMDLBL(END)
I appreciate any help
Thanks in advance
CHGVAR VAR(&DOCNAME) VALUE('/Archive/Folder/FileTransaction_' || &PRD || '.csv')
The SQL query produces a date value but I am getting the file result concatonated with SQL statement instead of the statement result.
The result should produce "/Archive/Folder/FileTransaction_201808.csv" but I getting the SQL statement concatonated instead.
DCL VAR(&SQL) TYPE(*CHAR) LEN(182)
DCL VAR(&DSQL) TYPE(*CHAR) LEN(22)
DCL VAR(&PRD) TYPE(*CHAR) LEN(70)
DCL VAR(&DPRD) TYPE(*CHAR) LEN(20)
DCL VAR(&DOCNAME) TYPE(*CHAR) LEN(45)
CHGVAR VAR(&DSQL) VALUE('DROP VIEW QTEMP/CASH1')
CHGVAR VAR(&DPRD) VALUE('DROP VIEW QTEMP/PRD')
CHGVAR VAR(&SQL) VALUE('CREATE VIEW QTEMP/CASH1 AS SELECT PORT_CD, PORT_NAME, SEC_CD, +
SEC_NAME, DIV_CPS, QTY, DIV_PRC, SEC_CCY, EX_RATE, VALUE_DT, AMOUNT_SC, +
AMOUNT_LC, EX_DATE FROM TESTLIB/OPTR_CASH')
CHGVAR VAR(&PRD) VALUE('CREATE VIEW QTEMP/PRD AS SELECT DISTINCT PRD FROM +
TESTLIB/OPTR_CASH')
/* Run SQL command to create qtemp view */
RUNSQL SQL(&DSQL) COMMIT(*NONE)
MONMSG MSGID(SQL0000)
RUNSQL SQL(&DPRD) COMMIT(*NONE)
MONMSG MSGID(SQL0000)
RUNSQL SQL(&PRD) COMMIT(*NONE)
MONMSG MSGID(SQL9999) EXEC(GOTO CMDLBL(EMAIL))
RUNSQL SQL(&SQL) COMMIT(*NONE)
MONMSG MSGID(SQL9999) EXEC(GOTO CMDLBL(EMAIL))
/* Copy record from created view into csv file */
CHGVAR VAR(&DOCNAME) VALUE('/Archive/Folder/FileTransaction_' || &PRD || '.csv')
CPYTOIMPF FROMFILE(QTEMP/CASH1) TOSTMF(&DOCNAME) MBROPT(*REPLACE) STMFCCSID(*PCASCII) +
RCDDLM(*CRLF) DTAFMT(*DLM) FLDDLM(',') ADDCOLNAM(*SQL)
MONMSG MSGID(CPF9999) EXEC(GOTO CMDLBL(EMAIL))
GOTO CMDLBL(END)
I appreciate any help
Thanks in advance
Comment