Including AND in the where clause only if a parameter <> 'Null'
Hitting an Oracle database thru SSRS. There are 15 parameters on the report. 13 are optional. The default value for the optional ones is a textual 'Null' So my where clause needs to have a few enforced conditions AND then 13 optional AND's. How do I make an AND statement get conditionally evaluated/optional SELECT * FROM MYTABLE A WHERE A.FIELD1 = 'Y' AND A.FIELD2 = 'TASER' AND A.FIELD3 = 'LASER' AND IF :P1 <> 'Null' then evaluate as AND :P1 = A.FIELD4 AND IF :P2 <> 'Null' then evaluate as AND :P2 = A.FIELD5 AND IF :P3 <> 'Null' then evaluate as AND :P3 = A.FIELD6 ETC... ---------- Any combination of the parameters may be used. ex: If P1 and P3 are anything but 'Null' then they both get evaluated. In this case 'Null' is the actual field value. It is not an empty value
Since the default value for optional parameter is literally 'Null', all you need to do is something like this: SELECT * FROM MYTABLE A WHERE A.FIELD1 = 'Y' AND A.FIELD2 = 'TASER' AND A.FIELD3 = 'LASER' AND (P1 = 'Null' OR P1 = A.FIELD4) AND (P2 = 'Null' OR P2 = A.FIELD5) AND (P3 = 'Null' OR P3 = A.FIELD6) AND (P4 = 'Null' OR P4 = A.FIELD7) /* ETC... */ The parentheses are important. If whatever parameter is passed with the default value of 'Null' then the OR inside of the parentheses will evaluate to true regardless of the value of the field. Otherwise, if some useful value is passed, it is going to have to be evaluated. Each line for the PN parameter can be restated like this: **AND P1 IN ('Null', A.FIELD4)** instead of **AND (P1 = 'Null' OR P1 = A.FIELD4)** Hope this helps, Oleg