Including AND in the where clause only if a parameter <> 'Null'

Question

question

mrwonderful asked Jul 19, '16

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

ssrs oracle script business-intelligence code

2

10 |1200

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

seanlange commented · Jul 20, 2016 at 07:20 PM

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 ·

Oleg commented · Jul 20, 2016 at 08:11 PM

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 ·

Answer 1 · 2016-07-20T18:08:35Z

Oleg answered Jul 20, '16

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

10 |1200

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

mrwonderful commented · Jul 20, 2016 at 08:30 PM

Thanks for the assist. Below is what I came up with: AND (:P1 = 'Null' OR ( NOT(:P1 = 'Null') AND :P1 = A.FIELD4) )

0 ·

mrwonderful commented · Jul 20, 2016 at 08:43 PM

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 ·

Oleg commented · Jul 20, 2016 at 08:44 PM

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 ·

question