SQL - Conditionally evaluate WHERE clauses

Question

I need to get a WHERE clause to only evaluate certain statements. something like:

WHERE field_a = field_b AND
   (CASE WHEN <PARAM>type</PARAM> = 5
      THEN {field_c = 1 OR field_c = 2}
      WHEN <PARAM>type</PARAM> = 6
      THEN {field_c = 3 OR field_c = 4}
      ELSE field_c = <PARAM>type</PARAM>

so that when the Param type = 5, it only checks if field_c = 1 or 2. Any thoughts?

Answer 1

WHERE field_a = field_b
AND (
    (type = 5 AND (field_c = 1 OR field_c = 2))
    OR (type = 6 AND (field_c = 3 OR field_c = 4))
    OR field_c = type
)

Is that what you're after?

Answer 2

WHERE 
((field_a = field_b)) AND
(((<PARAM>type</PARAM> = 5) AND (field_c IN (1, 2))) OR
((<PARAM>type</PARAM> = 6) AND (field_c IN (3, 4))) OR
((<PARAM>type</PARAM> NOT IN (5, 6) AND <PARAM>type</PARAM> = field_c)))

Answer 3

Would this do it?

Where field_a = field_b AND
(
   (<PARAM>type</PARAM> = 5 AND field_c IN (1,2))
   OR (<PARAM>type</PARAM> = 6 AND field_c IN (1,2))
   OR (field_c = <PARAM>type</PARAM>)
)

Answer 4

Create table temp:

type      |       field_c
5                 1 
5                 2
6                 3
6                 4


select yt.* from 
YourTable yt 
    INNER JOIN temp t 
on t.type = <type> and yt.field_c = field_c

Hard code must die! :)

Answer 5

Here's what I came up with, after some inspiration from the posts I realized I didn't need such conditional code. Also, Param type has the ability to accept multiple values, and outputs them separated by commas, which helps greatly.

WHERE field_a = field_b AND
   (field_c in (<PARAM>type</PARAM>))

Thanks for the help!

Answer 6

WHERE field_a = field_b AND
      CASE WHEN <PARAM>type</PARAM> = 5 THEN
                CASE WHEN field_c = 1 OR field_c = 2 THEN 1 ELSE 0 END
           WHEN <PARAM>type</PARAM> = 6 THEN
                CASE WHEN field_c = 3 OR field_c = 4 THEN 1 ELSE 0 END
           ELSE
                CASE WHEN field_c = <PARAM>type</PARAM> THEN 1 ELSE 0 END
      END = 1