opnqry

pts.
Tags:
OPNQRY
I have four selection parameters in a screen which is used as selection criteria in my opnqry.Ideally if all parameters are given the qryslt in opnqry will have all four fields in the select criteria. Now i have a scenario where if any of the selection parameter is not given in the screen the qryslt in opnqry should ignore this field in select criteria and have the remaining.Since there are 4 parameters there may be a lot of combinations and am slightly confused as to how to handle this. To be clear--If a=1,b=2,c=3,d=4 then qryslt will have a,b,c,d if a=1,b=2,c=3 then qryslt will have a,b,c.Likewise there could be many combinations. Please suggest

Answer Wiki

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

Hi,

You’ll need to build your query select statement based on the parameters given in :-

First fill your QRYSLT variable with the basic selection criteria (not dependent on your parameters):-
CHGVAR &QRYSLT ‘Basic slection criteria’

Then build the rest of your query select statement, based on which parameters are filled in :-
IF &A <> ” THEN CHGVAR &QRYSLT (&QRYSLT *CAT ‘*AND A =’ &A)
IF &B <> ” THEN CHGVAR &QRYSLT (&QRYSLT *CAT ‘*AND B =’ &B)
etc.

This is only to give you the basic idea, you’ll have to get the syntax right depending on whether you have numeric or character parameters.

Regards,

Martin Gilbert.

Discuss This Question: 4  Replies

 
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
  • sorry Am not clear..... wat i meant was depending on the combinations of select parameters given in the screen i require opnqry to select recrds from a file.So if user gives n number of combinations will i require n number of opnqry statements?
    0 pointsBadges:
    report
  • Gilly400
    Hi, You can do it in one OPNQRYF statement - If you build your statement up depending on the parameters filled in by your user. This is why the IF statements in the example above are used. Alternatively you could use one OPNQRYF statement with OR's. Something like this :- OPNQRYF QRYSLT((A=&A *OR &A = ' ') *AND (B=&B *OR &B = ' ') *AND (C=&C *OR &C = ' ')) Once again this is only to give you an idea of how to achieve this, the syntax may not work for you depending on your variables, etc. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • hey thanks i get wat u say...just confirm if this is right..i have two values in a screen date1 and date2.the user can either fill in the date values or leave it blank...if dates are given then my qryslt will have date1=12122008 and date2=12122008.case 2 is wen date1 iss blank then qryslt have only date2=12122008 ..if there are two more combinations then 2 more qry are required...this seems ok for few comb but if there are more nos is there an alternate way...if this is the only way i will continue with the same
    0 pointsBadges:
    report
  • philpl1jb
    No, this can be very flexible. You can build the string based on the fields used by the user (Sample below) then use that sting in the QRYSLT part of the ovrdbf. The trick, as you know, is building it with the absolute correct format. DCL &QRYSLT *CHAR 256 ... if (&FLD1 <> ' ') then do chgvar &QRYSLT ('MyField = ''' *cat &Fld1 *cat ''') enddo if (&FLD2 <> ' ') then do if &QRYSLT <> ' ' then do CHGVAR &QRYSLT (%TRIM(&QRYSLT) *cat ' *AND ') enddo CHGVAR &QRYSLT (%TRIM(&QRYSLT) *cat ' MyField2 = ''' *cat &Fld2 *cart ''') enddo Phil
    49,435 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