3

I need that when var1 is equal to 'Y' the query show the result of the table with the column = 'Y', but when var1 = 'N' the result should be the data with column with 'Y' and 'N'. I need to put it in a where clause, cause I'm using oracle forms. I tried this way but the query didn't show any result:

SELECT  *
FROM    table
WHERE   column1 IN ((CASE WHEN var1 = 'Y' THEN q'[('Y')]'
                                  ELSE TO_CHAR(q'[('Y','N')]')
                                  END))

Can you help me? Thank you.

Andrii Omelchenko
  • 13,183
  • 12
  • 43
  • 79
dnlopezm
  • 33
  • 6

1 Answers1

2

There is no need for CASE logic here, as you can fit this into a regular WHERE clause with boolean logic, wrapping each condition (var1 = 'Y', var1 <> 'Y') in a () group.

SELECT *
FROM table
WHERE
  (var1 = 'Y' AND column1 = 'Y')
  OR (var1 <> 'Y' AND column1 IN ('Y','N'))

Note, I used var1 <> 'Y' here to emulate your ELSE case, but if it is only two possible values Y/N you may use var1 = 'N' for clarity.

WHERE
  (var1 = 'Y' AND column1 = 'Y')
  OR (var1 = 'N' AND column1 IN ('Y','N'))

Actually, if Y/N are the only possible values for column1, then it could be simplified to:

WHERE
  (var1 = 'Y' AND column1 = 'Y')
  -- Returns all rows for column1 if Y,N are the only possible values
  -- No need to explicitly filter it
  OR (var1 <> 'Y')
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • This worked for me, I tried this solution using CASE WHEN: http://stackoverflow.com/questions/15396217/if-statement-within-where-clause; but I need to have IN statements and NOT NULL as the default, this OR combination worked well. Ensure you have Parens around the entire OR statement if you have additional AND statements. – CigarDoug Oct 10 '13 at 12:36