ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need help with passing SQL result into value

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with passing SQL result into value

    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

  • #2
    You need to define your view with DCLF and after having created the view, you have to read it with RCVF like any other file.
    After having read the view (record) &PRD should be populated.

    Birgitta

    Comment

    Working...
    X