NULL Values??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: NULL Values??

  1. #1
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Question NULL Values??

    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???>
    RP Kumar
    You Can Win, if u believe Yourself

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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
    -

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

  5. #5
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Null

    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
    RP Kumar
    You Can Win, if u believe Yourself

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Null

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width