Problem with null in SQL query?

Question

select 
    case 
       when ''!='abcd'
           then 'nodate'
       else 'date'
    end;

Here I hope '' stand for null, but when I execute this query I am getting 'date' as an ouput, rather than 'nodate'. What is the problem in this query? Why does ''!='abcd' condition checking become true?

you have problem in your problen

Dejan Marjanović
– Dejan Marjanović

2011-03-31 11:44:58 +00:00
Commented Mar 31, 2011 at 11:44 — Dejan Marjanović
– Dejan Marjanović, Commented Mar 31, 2011 at 11:44
hi webarto.. what is problen?

jcrshankar
– jcrshankar

2011-03-31 11:45:42 +00:00
Commented Mar 31, 2011 at 11:45 — jcrshankar
– jcrshankar, Commented Mar 31, 2011 at 11:45

onedaywhen · Accepted Answer · 2011-03-31 15:01:02Z

2

After looking at comments for this answer, here is the explaination -

In your example, '' is considered as NULL in Oracle and hence '' != 'abcd' is evaluated to UNKNOWN, which is the reason the 'else clause' result is returned (and not the 'when clause' result).

If you want to check some column for NULL explicitly mention it.

...case when columnName is NULL then ... else ... end

or if you are checking for some column value to be 'abcd' then use -

...case when columnName != 'abcd' then ... else ... end

edited Mar 31, 2011 at 15:01

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Mar 31, 2011 at 11:49

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

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

8 Comments

jcrshankar Over a year ago

sachin could u please look at the my query again?i updated now. as per your ans the condition is true, then it should return'nodate' right.why its returing 'date'?

Dave Costa Over a year ago

Actually, in Oracle '' is the same as NULL. However, an equality test doesn't work for NULL: NULL = NULL would not be true. So you do need to use IS NULL to test if a value is NULL.

Rob van Wijk Over a year ago

-1 because your first four sentences are false. Oracle doesn't know the difference between the empty string and NULL: they are both considered NULL.

ypercubeᵀᴹ Over a year ago

@Sachin: NULL != 'abcd' should evalute to NULL in all decent rdbms systems, Oracle included...

Dave Costa Over a year ago

@Sachin: Because NULL never produces TRUE in any comparison except IS NULL. NULL indicates an unknown value, so both NULL = x and NULL != x are considered unknown conditions, regardless of the value of x.

|

ypercubeᵀᴹ · Accepted Answer · 2011-03-31 13:28:57Z

1

If you want check for NULL, try this:

value IS NULL

So, if you want to replace '' with NULL, don't put NULL != 'abcd' but:

'abcd' IS NOT NULL

if there is a variable there and not '', try:

IF (l_variable != 'abcd) or (l_variable IS NULL)

It would be better however, to post complete code and not a modified part of it.

edited Mar 31, 2011 at 13:28

answered Mar 31, 2011 at 11:52

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Comments

Tobias · Accepted Answer · 2011-03-31 11:51:11Z

0

use NULL to check if the value is NULL, also see http://www.dba-oracle.com/t_case_statement_else_null_value.htm

answered Mar 31, 2011 at 11:51

Tobias

7,40311 gold badges51 silver badges82 bronze badges

Comments

DRapp · Accepted Answer · 2011-03-31 11:52:33Z

0

I think what you want is

case when YourColumn is null then 'nodate' else 'yes date' end

answered Mar 31, 2011 at 11:52

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

2 Comments

jcrshankar Over a year ago

in my PL/SQL program i jus declared a variable as l_variable varchar(20); In later part of my program i used this variable to compare with a string... if l_variable != 'abcd' then statements; end if; according to the condition the program should go inside the if loop but thats not happening in my case.

ypercubeᵀᴹ Over a year ago

Try: IF (l_variable != 'abcd) or (l_variable IS NULL)

Collectives™ on Stack Overflow

Problem with null in SQL query?

4 Answers 4

8 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related