OPNQRYF
I have 4-fileds on Display File like frdiv(zoned),todiv(zoned),frpr(char),topr(char) all the fields are of size 2 only.
I am using a "OPNQRYF" to filter the records froma Physical file (pfdiv), it containes 1,25,000 records .
Through this openqryf all the selected records I will copy to a file (pftemp) using "CPYFRMQRYF".
Then I will use the pftemp file in the RPG/400 programme to disply on Subfile Screen.
I have written a CL-programme like this:
PGM
DCLF FILENAME(DISPLAY-FILE)
OVRDBF PFDIV(PHYSICAL-FILE)
OPNQRYF QRYSLT('(&FDIV *GE &FRDIV *AND &FDIV *LE &TODIV) *AND (&FPR *GE &FRPR *AND &TPR *LE &TOPR)')
CPYFRMQRYF FROMF(PFDIV) TO(PFTEMP)
CALL PGM(RPG)
CLOF PFDIV
DLTOVR PFDIV
ENDPGM
WHERE &FDIV = FROM DIVISION
&TDIV = TO DIVISION
&FPR = FROM PROJECT
&TPR = TO PROJECT
I am entering values 10,50,A1,A9 respectively
10,50 means from 10(division) to 50
A1,A9 means from A1(Project) to A9
ALL THE ABOVE FIELDS FROM THE PHYSICAL-FILE (PFDIV)
Here , once I am running this programme the query is not working properly, it is showing an error ,
Please can anyone help me in this regards.
Thanks In Advance.
Software/Hardware used:
Software/Hardware used:
ASKED:
February 1, 2009 2:21 PM
UPDATED:
February 2, 2009 5:52 PM
Answer Wiki:
Hi Khadar
OPNQRY generates frequent questions
1. If you are going to use the opnqry on subsequent programs you should
use SHARE(*YES) on the OVRDBF then you do not need the CPYFRMQRYF when the override is in effect it will look like the PFDIV only contains the selected fields.
2. OPNQRY should include the file name --PFDIV
3. The QRYSLT string is sent to the database engine without interpertation.. you are sending this
&FDIV *GE &FRDIV *AND &FDIV *LE &TODIV) *AND (&FPR *GE &FRPR *AND &TPR *LE &TOPR)')
you must send something that looks a lot like SQL --
(FDIV *GE 10 *AND FDIV *LE 50) *AND (FPR *GE 'A1' *AND FPR *LE 'A9')
where FDIV and FPR are fields in the file
To build this string DCL a string var
CHGVAR SLTSTR ('(FDIV *GE '
*CAT &FRDIV
*CAT ' *AND FDIV *LE '
*CAT &TODIV
*CAT ') *AND (FPR *GE '''
*CAT &FRPR
*CAT ''' *AND FPR *LE '''
*CAT &TOPR
*CAT ''')')
Then use this string in the qryslt clause of the OPNQRYF
Where the string needs a quote in the output to deliniate a string value put
two '' which CL reads as insert a quote not as terminate a string.
Where we need both an insert a quote and a terminate a string you actually put in 3 ''' s
good luck
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
OPNQRYF has a built-in function of %RANGE. This can simplify a select statement.
For example, to select a range of divisions the select statement would include ‘DIVISION = %Range(‘ *CAT &FDIV *BCAT &TDIV *CAT ‘)’
Another hint – be sure the variables concatenated into your query select string are in character format. I’ve found that OPNQRYF is a lot happier with character data in the select statement.