question

mrwonderful avatar image
mrwonderful asked

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
ssrsoraclescriptbusiness-intelligencecode
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You might take a peek at this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ I would ask why you are defaulting text to a string literal 'NULL'. Why not let it be NULL?
0 Likes 0 ·
Even better question is why default to the literal string 'Null' which makes the matters even worse because Oracle string comparisons are case sensitive so there is an additional risk of unexpected results :)
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the assist. Below is what I came up with: AND (:P1 = 'Null' OR ( NOT(:P1 = 'Null') AND :P1 = A.FIELD4) )
0 Likes 0 ·
Because NULL is just a unioned in value for the dropdown list in SSRS. It is not actually a value on any record in the table
0 Likes 0 ·
And what was the purpose of the part reading **NOT(:P1 = 'Null') AND** ? Just highlight it, press Backspace button, and the resulting **AND (:P1 = 'Null' OR :P1 = A.FIELD4)** will still do the trick.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.