CODEWITHSUNDEEP
sqloracle-database
IT ppl
IT ppl

Reputation: 2647

Case when statement in SQL

I am using the following query. In this query I want to apply the where clause based on passed parameter. But the issue is that where clause is like 'value = if parameterVal = 'I' than NULL else NOT NULL'

I've build a query like this

SELECT * FROM MASTER
WHERE
Column1 IS (CASE WHEN :Filter = 'I' THEN 'NULL' ELSE 'NOT NULL' END)

but it's not working. Help me solve this.

UPDATE

Updating question to elaborate question more clearly.

I've one table MASTER. Now I am passing one parameter in query that is Filter (indicated by :Filter in query).

Now when the Filter parameter's value is 'I' than it should return the following result.

SELECT * FROM MASTER WHERE Column1 IS NULL

but if the passed argument is not equal to 'I' than,

SELECT * FROM MASTER WHERE Column1 IS NOT NULL

Upvotes: 0

Views: 109

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

If you really insist on using a CASE the SELECT could be rewritten as:

SELECT *
  FROM MASTER
  WHERE CASE
          WHEN COLUMN1 IS NULL AND FILTER = 'I' THEN 1
          WHEN COLUMN1 IS NOT NULL AND FILTER <> 'I' THEN 1
          ELSE 0
        END = 1

SQLFiddle here

Frankly, though, I think that this is very difficult to interpret, and I suggest that @MAli's version is better.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69594

SELECT * FROM MASTER
WHERE  (Filter = 'I'  AND Column1 IS NULL)
     OR 
       (Filter <> 'I' AND Column1 IS NOT NULL)

Upvotes: 4

Newbie
Newbie

Reputation: 1

Your case has assignment not equality check

Upvotes: -3

Related Questions