Reputation: 2647
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
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
Frankly, though, I think that this is very difficult to interpret, and I suggest that @MAli's version is better.
Upvotes: 1
Reputation: 69594
SELECT * FROM MASTER
WHERE (Filter = 'I' AND Column1 IS NULL)
OR
(Filter <> 'I' AND Column1 IS NOT NULL)
Upvotes: 4