Query Manager Query

[Guest.Visitor]

I need to calculate a date in RPGLE, call a CL from the RPG passing the date. In the CL is a QMQRY that would like to use this date parm as a variable in the select statement to write to an outfile. I won't have a problem with the RPG or creating the file. The piece of the code in the query looks like this "...and a.ddate = &COND group by a.dstr, a.ddptno..." The CL query looks like this where &DATEIN in the passed date from the RPG and is defined as *char length of 10. STRQMQRY QMQRY(QUERY/DLYITMQRY) OUTPUT(*OUTFILE) + OUTFILE(TEST1/JACKTEST) OUTMBR(*FIRST + *ADD) SETVAR((COND &DATEIN)) In trying to just run the CL passing '2000-12-04' as the parm, the query prompts me for a date (don't understand why when I'm already passing it one). When I enter the same date,it hurls giving me message QWM2701. Anyone ever make this work? thanks - Jack

[Guest.Visitor]

You need to define &DATEIN as CHAR LEN(12) not LEN(10) and tack single quotes around &DATEIN like: CHGVAR VAR(&DATEIN) VALUE(X'7D' *TCAT &DATEIN *TCAT X'7D') Query otherwise is replacing the string in your select statement exactly as you pass it in - without quotes. Chris

[GlenKerner]

When you prompt in QM you must run it in batch in order for it to actually use the variables in the CL. Running it interactively will alway prompt for the value.

[Guest.Visitor]

Chris, I tried the statement as you sent it, and get "operand not valid or operator missing in Value"

[GlenKerner]

Here is one of my QM'sSELECT -- Columns A.MLFILE, A.MLLIB, A.MLFATR, A.MLNAME, A.MLNRCD, A.MLNDTR, A.MLS A.MLCDAT, A.MLUDAT, A.MLMTXT -- Tables FROM "GLENLIB"/"FILES" A -- Row Selection WHERE ((A.MLUDAT = &BASE) AND ((CHAR(A.MLSIZE)) >= &SIZE)) -- Sort Columns ORDER BY A.MLSIZE DESC, A.MLFILE, A.MLNAME, A.MLLIB, A.MLFATR, A A.MLNDTR, A.MLCDAT, A.MLUDAT, A.MLMTXT The portion of the CL that deals with the variables is: DCL VAR(&TIMEFRAME) TYPE(*DEC) LEN(15 0) DCL VAR(&SIZELIMIT) TYPE(*DEC) LEN(15 0) DCL VAR(&TIMETYPE) TYPE(*CHAR) LEN(1) DCL VAR(&TYPE) TYPE(*CHAR) LEN(1) DCL VAR(&BASEDATE) TYPE(*CHAR) LEN(10) DCL VAR(&CURDAT) TYPE(*CHAR) LEN(10) DCL VAR(&DATE) TYPE(*CHAR) LEN(6) DCL VAR(&DTEFMT) TYPE(*CHAR) LEN(3) DCL VAR(&DATFMT) TYPE(*CHAR) LEN(4) DCL VAR(&QUOTE) TYPE(*CHAR) LEN(1) VALUE('''') DCL VAR(&DATEPASS) TYPE(*CHAR) LEN(8) DCL VAR(&SIZLMT) TYPE(*CHAR) LEN(15) DCL VAR(&SIZELMT) TYPE(*CHAR) LEN(17) CALL PGM(GLENLIB/DATECALC) PARM(&CURDAT + &TIMEFRAME &BASEDATE) CVTDAT DATE(&BASEDATE) TOVAR(&DATE) FROMFMT(*ISO) + TOFMT(*YMD) TOSEP(*NONE) CHGVAR VAR(&DATEPASS) VALUE(&QUOTE *CAT &DATE *CAT + &QUOTE) CHGVAR VAR(&SIZLMT) VALUE(&SIZELIMIT) CHGVAR VAR(&SIZELMT) VALUE(&QUOTE *CAT &SIZLMT + *CAT &QUOTE) STRQMQRY QMQRY(GLENLIB/FILEOLD) OUTPUT(*OUTFILE) + OUTFILE(GLENLIB/FILEOLD) SETVAR((BASE + &DATEPASS) (SIZE &SIZELMT)) I hope this helps.

[Guest.Visitor]

John, Sorry. I typed that "on the fly". chgvar var(&quote) value(X'7D') chgvar var(&Date1) value(&quote *tcat &date *tcat &quote) Chris

[Guest.Visitor]

Define the variable in the QM statement with capitals: &INDATE; that may solve the prompting. In batch you will get an MSGW-job because it still wants to prompt and cannot, obviously, do that. Regards, Carel Teijgeler

[Absolutely_Nobody]

Here's the CL and QM SQL that I used for my parameterized query..... PGM PARM(&PARMS) DCL &PARMS *CHAR 30 DCL &SDATE *CHAR 8 DCL &EDATE *CHAR 8 DCL &LIT1 *CHAR 12 DCL &LIT2 *CHAR 12 DCL &LIT3 *CHAR 5 DCL &DTLSUM *CHAR 1 DCL &AVGGE *CHAR 3 DCL &CUST *CHAR 10 DCL &CUSTQ *CHAR 33 DCL &CONSTANT *CHAR 21 VALUE('AND ZARPSTL4.BILLTO =') DCL &BLANKS *CHAR 21 VALUE(' ') CHGVAR &SDATE VALUE(%SST(&PARMS 1 8)) CHGVAR &EDATE VALUE(%SST(&PARMS 9 8)) CHGVAR &DTLSUM VALUE(%SST(&PARMS 17 1)) CHGVAR &AVGGE VALUE(%SST(&PARMS 18 3)) CHGVAR &CUST VALUE(%SST(&PARMS 21 10)) CHGVAR &LIT1 VALUE('''' |< (%SST(&SDATE 1 4)) |< '-' + |< (%SST(&SDATE 5 2)) |< '-' + |< (%SST(&SDATE 7 2)) |< '''') CHGVAR &LIT2 VALUE('''' |< (%SST(&EDATE 1 4)) |< '-' + |< (%SST(&EDATE 5 2)) |< '-' + |< (%SST(&EDATE 7 2)) |< '''') OVRPRTF FILE(QPQXPRTF) PAGESIZE(66 132) LPI(6) + CPI(12) USRDTA('Avg Pay') OVRSCOPE(*JOB) MONMSG MSGID(CPF0000) IF COND(&DTLSUM *EQ 'S') THEN(GOTO CMDLBL(SUMRPT)) IF COND(&CUST > &BLANKS) THEN(DO) CHGVAR &CUSTQ VALUE(&CONSTANT |< '''' |< &CUST |< '''') ENDDO ELSE DO CHGVAR &CUSTQ VALUE(&BLANKS) ENDDO STRQMQRY QMQRY(*LIBL/ARDIOQ01) OUTPUT(*PRINT) + QMFORM(*LIBL/ARDIOF01) SETVAR((START &SDATE) + (END &EDATE) (CUST &CUSTQ) (LIT1 &LIT1) (LIT2 &LIT2)) GOTO CMDLBL(END) SUMRPT: CHGVAR &LIT3 VALUE('''' |< &AVGGE |< '''') STRQMQRY QMQRY(*LIBL/ARDIOQ02) OUTPUT(*PRINT) + QMFORM(*LIBL/ARDIOF02) SETVAR((START &SDATE) (END &EDATE) + (AVGGE &AVGGE) (LIT1 &LIT1) (LIT2 &LIT2) (LIT3 &LIT3)) END: RETURN ENDPGM select DISTINCT zarpstl4.billto, cmname, cmtrms, zarpstl4.docinv, (SUBSTR(DIGITS(ZARPSTL5.DTETRN),1,4))||'-'|| (SUBSTR(DIGITS(ZARPSTL5.DTETRN),5,2))||'-'|| (SUBSTR(DIGITS(ZARPSTL5.DTETRN),7,2)) AS INVDATE, (SUBSTR(DIGITS(ZARPSTL4.DTETRN),1,4))||'-'|| (SUBSTR(DIGITS(ZARPSTL4.DTETRN),5,2))||'-'|| (SUBSTR(DIGITS(ZARPSTL4.DTETRN),7,2)) AS PAYDATE, DAYS(((SUBSTR(DIGITS(ZARPSTL4.DTETRN),1,4))||'-'|| (SUBSTR(DIGITS(ZARPSTL4.DTETRN),5,2))||'-'|| (SUBSTR(DIGITS(ZARPSTL4.DTETRN),7,2)))) - DAYS(((SUBSTR(DIGITS(ZARPSTL5.DTETRN),1,4))||'-'|| (SUBSTR(DIGITS(ZARPSTL5.DTETRN),5,2))||'-'|| (SUBSTR(DIGITS(ZARPSTL5.DTETRN),7,2)))) AS DAYS2PAY, &LIT1, &LIT2 from LHCUSM01, Zarpstl4 inner join zarpstl5 on zarpstl5.billto = zarpstl4.billto and zarpstl5.docinv = zarpstl4.docinv where zarpstl4.dtetrn between &START and &END &CUST AND zarpstl4.dtetrn > zarpstl5.dtetrn AND COCO = zarpstl5.MFG AND CMARCN = zarpstl4.billto order by zarpstl4.billto, zarpstl4.docinv

[Guest.Visitor]

Thanks to all for the advice - it was the "all caps" on the variable name that finally did it. Now the query is submitted and builds the file; just as advertised.