OPNQRYF

[pantherv]

I want to code an OPNQRYF statement in a CL program, not sure this is the right forum but here goes. It should have a QRYSLT statement along the lines of LRDTE = %RANGE(&DATE1 &DATE2) *AND LROUT = %RANGE(&ROUTEF &ROUTET)

I have tried all sorts of combinations using ''' ( ) || *CAT *BCAT etc but after compilation the CL program errors ar the OPNQRYF statement. All I need is the right format for the QRYSLT. Can anyone help or point me in the right direction? Even copying a similar QRYSLT from another CL and amending wouldn't work.

 

[ladeecroft]

hi!

what is the exact error message you've encountered when you run the CL pgm?

i think it would be advisable to use a variable in your CL pgm for the query selection instead of specifying the criteria directly in the OPNQRYF statement. This way, you'll be able to check (watch the variable in STRISDB) if the criteria you've specified in the variable is correct.

 

[jmd0252]

sounds like something i wanted to do a while back,,, I will paste in the code I use,,, got it from the programmers here and about,,, check also the IBM server forum.

**********************************************************/
PGM

DCL VAR(&RFDAT#) TYPE(*DEC) LEN(6 0)
DCL VAR(&TFDAT#) TYPE(*DEC) LEN(6 0)
DCL VAR(&RFDAT) TYPE(*CHAR) LEN(6)
DCL VAR(&TFDAT) TYPE(*CHAR) LEN(6)
SNDUSRMSG MSG('ENTER MONTH BEGIN DATE - MMDDYY') +
TOMSGQ(*) MSGTYPE(*INQ) MSGRPY(&RFDAT)
SNDUSRMSG MSG('ENTER MONTH ENDING DATE - MMDDYY') +
TOMSGQ(*) MSGTYPE(*INQ) MSGRPY(&TFDAT)

CHGDTAARA DTAARA(*LDA (1 6)) VALUE(&RFDAT)
CHGDTAARA DTAARA(*LDA (7 6)) VALUE(&TFDAT)

CALL PGM(LABDATE) PARM(&RFDAT# &TFDAT#)


CHGVAR VAR(&RFDAT) VALUE(&RFDAT#)
CHGVAR VAR(&TFDAT) VALUE(&TFDAT#)

CLRPFM FILE(HCCCUSTOM/LABWKP)

OVRDBF FILE(F0911) TOFILE(*LIBL/F0911) SHARE(*YES)

OPNQRYF FILE((F0911)) OPTION(*INP) +
QRYSLT('GLDGJ *EQ +
%RANGE(' *CAT &RFDAT *CAT ' ' *BCAT &TFDAT *CAT ')')



I was changing some dates to julian, so I could choose the range in the open qry statement. Hope this helps

 

[as400pro]

I like to use a variable to build my select statement so I can easily add or remove selection criteria.
I have used this technique for years.
Below is code from a working program. It is selecting orders of type 'LP' billed between the selected dates.

DCL VAR(&SEL) TYPE(*CHAR) LEN(1024)
DCL VAR(&DATE1) TYPE(*CHAR) LEN(8)
DCL VAR(&DATE2) TYPE(*CHAR) LEN(8)
DCL VAR(&LP) TYPE(*CHAR) LEN(2) VALUE(LP)

CHGVAR VAR(&DATE1) VALUE(%SST(&PARM 201 8))
CHGVAR VAR(&DATE2) VALUE(%SST(&PARM 209 8))

CHGVAR VAR(&SEL) VALUE('#COFBD *EQ %RANGE(' +
*CAT &DATE1 *CAT ' ' *CAT &DATE2 +
*CAT ')')

CHGVAR VAR(&SEL) VALUE(&SEL *BCAT ' *AND #COTYP +
*EQ' *BCAT '''' || &LP || '''')

OVRDBF FILE(CUSORD00) SHARE(*YES)

OPNQRYF FILE((CUSORD00)) QRYSLT(&SEL) KEYFLD(*FILE)

CALL PGM(PORR100) PARM(&PARM)

CLOF OPNID(CUSORD00)
DLTOVR *ALL

T. Bishop

http://www.as400pro.com

 

[pantherv]

Thanks for the replies.

Now have:

CHGVAR VAR(&QRYSLT) VALUE('(LRDTE = %RANGE(' || +
&DATE1 *BCAT &DATE2 || ')) *AND (LROUT +
*GE "' *TCAT &ROUTEF *TCAT '&quot *AND (LROUT +
*LE "' *TCAT &ROUTET *TCAT '&quot')

OPNQRYF FILE((ECLL33)) OPTION(*INP) QRYSLT(&QRYSLT) +
KEYFLD(*FILE) SEQONLY (NO)

Which seems to work. Time for a user test....

 

[pantherv]

ladeecroft - after changing the CL to use a variable ISDB showed that &ROUTEF was the problem as it can be blank and %RANGE had problems with that. Using *GE and *LE as above is able to cope with the blank. Thanks for that pointer.

 

[ladeecroft]

No prob pantherv...that's what we're all here for, to help each other =)