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
Software/Hardware used:
Software/Hardware used:
ASKED:
December 29, 2008 12:19 PM
UPDATED:
December 29, 2008 7:14 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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?
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.
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
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