CODEWITHSUNDEEP
sqloracle-databaseparameters
Jamie Taylor
Jamie Taylor

Reputation: 3530

Oracle SQL Case statement with NULL values

I'm trying to create an Oracle query that will use a parameter based on the CommandName of a button.

So here's my query

SELECT id 
FROM table 
WHERE dateTime IS (CASE WHEN :CommandName = 'FIRST' THEN NULL ELSE NOT NULL END)

So I'm passing the parameter but it's not working - I'm just getting a Missing NULL keyword error

I basically don't want to have to write two separate queries based on the parameter that is input

Not even sure if this is possible or if this is the right way of doing it?

Any ideas?

Upvotes: 2

Views: 15005

Answers (3)

John D
John D

Reputation: 2365

You may want to read up on the CASE statement a bit more.

I believe you want the following code:

SELECT
    id 
FROM 
    table 
WHERE 
    (
        (dateTime IS NULL AND :CommandName = 'FIRST')
        OR
        (dateTime IS NOT NULL AND NVL(:CommandName, '') <> 'FIRST')
    )

Upvotes: 4

WoMo
WoMo

Reputation: 7266

Without using dynamic SQL to return the string 'NULL' or 'NOT NULL' you could use two NVL() functions:

select id
from table
where NVL(dateTime,'FIRST') = NVL(:param,dateTime);

However, if dateTime is indexed (and Oracle can index NULLs), ths NVL() function will disable the index.

Upvotes: 1

fudo
fudo

Reputation: 219

I think that

SELECT id 
FROM table 
WHERE (dateTime IS NULL AND :CommandName='FIRST') OR (dateTime IS NOT NULL AND :CommandName <> 'FIRST')

should do what you need.

Upvotes: 0

Related Questions