CODEWITHSUNDEEP
oracle-database
totalitarian
totalitarian

Reputation: 3666

Oracle - how to do this query? IF ELSE IN() clause

This is my simplified query

SELECT FILTER_IS_LONGS FROM PO_OPEN_FINAL_REPORT 
WHERE FILTER_IS_LONGS in( CASE WHEN testVar = 1 then 'Y' else 'N','Y' END )

I get a "missing keyword" error.

The query should basically generate an IN() clause of IN('Y') or IN('N','Y') depending on the value of testVar

I guess it is something to do with escaping 'N','Y' any help would be appreciated

Upvotes: 1

Views: 41

Answers (2)

Álvaro González
Álvaro González

Reputation: 146460

Totally untested, just to give you an idea:

SELECT FILTER_IS_LONGS
FROM PO_OPEN_FINAL_REPORT 
WHERE (testVar=1 AND FILTER_IS_LONGS='Y')
OR (testVar<>1 AND FILTER_IS_LONGS IN ('N','Y'))

It's probably identical to:

SELECT FILTER_IS_LONGS
FROM PO_OPEN_FINAL_REPORT 
WHERE FILTER_IS_LONGS='Y'
OR (testVar<>1 AND FILTER_IS_LONGS='N')

Upvotes: 1

HamoriZ
HamoriZ

Reputation: 2438

What about

SELECT FILTER_IS_LONGS FROM PO_OPEN_FINAL_REPORT 
WHERE 
(case 
when testvar=1 and FILTER_IS_LONGS in ('Y') then 1 
when testvar<>1 and FILTER_IS_LONGS in ('Y','N') then 1 
else 0) = 1

Upvotes: 1

Related Questions