NULL Values??

[Kumar_RP]

Hi,
SELECT DECODE(CHR(0), null, 'NULL', 'NOT NULL') from dual;

DECODE(C
--------
NOT NULL

Why it is 'NOT NULL' instead of 'NULL'????

Any one clarify this???>

[gandolf989]

I looked at the expression with the NVL function instead of DECODE which doesn't handle nulls anyway. If the first part of an NVL expression is NULL it returns the second part. So in example 1 I passed in NULL and Oracle returned 'NULL', in the second I passed in CHR(0) and Oracle returned '' or nothing. Which I think is the way Oracle treats CHR(0).

1* SELECT NVL(NULL, 'NULL') from dual
SQL> /

NVL(
----
NULL

SQL> SELECT NVL(CHR(0), 'NULL') from dual
2 /

N
-

[jmodic]

Why should it be 'NULL'?

CHR(0) is a valid ASCII character, it certanly is not the same as NULL.

If you want your DECODE to return 'NULL' then you must use some CHR() expression that results in NULL. Like the following, if you pass negative integer to the CHR() function:

SELECT DECODE(CHR(-1), null, 'NULL', 'NOT NULL') from dual;

DECODE(C
--------
NULL

[gandolf989]

I have an ascii chart that lists 0 as NUL, which if Kumar_RP was looking at something similar might be the source of the confusion.

Apparently Decode doesn't have a problem with nulls, I'm not sure where I got that from. I stand corrected.

[Kumar_RP]

Hi,
I fully agree with the explanation given by gandolf989.
Actually my problem is that I am loading data using SqlLoader into a table TEMP_SUBS.

Desc TEMP_SUBS

Sbcr_Id number(9)
Sbcr_Code varchar2(1)

Some junk values are coming in Sbcr_Code field.
When I query the table....

select count(*) A,nvl(sbcr_Code,'n') B
from temp_subs
group by nvl(sbcr_Code,'n')

I get the following result :

Output
A B
------------------
53078
27047 R
8790 S
9985 T
67983 n


select count(*) from temp_subs
where sbcr_code =' ' ---Checking for Space

Output:
-------
No Rows Selected

select count(*) from temp_subs
where sbcr_code is null ---Checking for Null

Output:
-------
67983

Select count(*),ascii(sbcr_code) from temp_subs
group by ascii(sbcr_code);

Output
-------
53078 0
27047 82
8790 83
9985 84
67983


And Ascii of NULL is Zero !!!

Which means that these 53078 are also having value as NULL ???
But NVL function is not working these cases and condition "Where sbcr_code is null" returns only 67983 rows.

Does oracle treats chr(0) different fron Null ??
If yes then why ??
Plz also suggest some suitable way to get rid on these values while
loading my table.

Ofcourse, I don't want to update these values to Null every time.

Thanks

[jmodic]

Originally posted by Kumar_RP
And Ascii of NULL is Zero !!!

Wher did you get this idea from? ASCII character 0 is named only NUL, but has nothing in common with NULL value in relational databases. NULL value from database has no ascii representation whatsoever, because it is something which is undefined, something without any particular value.

Which means that these 53078 are also having value as NULL ???
Does oracle treats chr(0) different fron Null ??

Of course it treats NULL diferently as CHR(0)! NULL in relational databases has nothing in common with ascii character represented by decimal number 0. CHR(0) is simply one of the ascii characters, while NULL is unknown/undefined value that has no representation. LENGTH(CHR(0)) = 1, while LENGTH(NULL) is undefined (is NULL). CHR(0)=CHR(0) evaluates to TRUE, while NULL=NULL doesn't evaluets to TRUE neither to FALSE. Etc etc...

Plz also suggest some suitable way to get rid on these values while loading my table.
Ofcourse, I don't want to update these values to Null every time.

Plenty of options. If you want to prevent the CHR(0) values only while loading data through SQL*Loader you can use SQL*Loader's NULLIF expression in your controlfile (... NULLIF sbcr_Code = CHR(0) ...). Or to prevent entry of CHR(0) in general, you can create row trigger on that table that will convert any occurance of CHR(0) to NULL.