Oracle SQL Case statement with NULL values

Question

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?

John D · Accepted Answer · 2012-07-23 21:35:04Z

4

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')
    )

edited Jul 23, 2012 at 21:35

answered Jul 23, 2012 at 17:12

John D

2,37517 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jamie Taylor Over a year ago

thanks for the info - Is it better to use != or <> different places say different things

John D Over a year ago

<> is ANSI standard while != is not compatible in all database systems (though in this case it is in the vast majority).

John D Over a year ago

Ah good spot - updated the SQL to have an NVL when checking if it does not equal FIRST

WoMo · Accepted Answer · 2012-07-23 17:12:28Z

1

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.

answered Jul 23, 2012 at 17:12

WoMo

7,2862 gold badges31 silver badges37 bronze badges

3 Comments

John D Over a year ago

Ah interesting way though based on the assumption :param is FIRST or NULL

jva Over a year ago

Oracle will evaluate "NULL = NULL" to NULL, so it will not return anything in that case.

WoMo Over a year ago

@jva True, but the first NVL() on the left side of the operator provides an alternative value when dateTime is null ('FIRST'), so the first expression is never null. IF :param is null, the dateTime column will be used for all rows, and any rows where datetime is in fact null will be discarded since 'FIRST' = NULL evaluates to false. That said, I'm digging @John D's solution with the OR operator, although I'd wrap that mutually exclusive pair of conditions in parenthesis so other predicates will be applied as expected.

fudo · Accepted Answer · 2012-07-23 17:11:42Z

0

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.

answered Jul 23, 2012 at 17:11

fudo

2191 silver badge13 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Case statement with NULL values

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related