OPNQRYF query not working to filter pfdiv records

90 pts.
Tags:
CL/400
Copy from Query File
CPYFRMQRYF
Open Query File
OPNQRYF
Physical File
RPG/400
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 from a Physical file (pfdiv), it contains 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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • WoodEngineer
    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.
    6,725 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following