Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Unhappy Unanswered: checking for NULL field!

    hi all,

    I want to check whether the value of the column of a table is NULL or not.

    I tested like

    if ( columnname = NULL) then
    ....
    else
    ...
    end if

    but here even if the particular column has no value 'ELSE' part is executing.


    Anybody can help me?
    THanx in advance.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    hi,

    Use the IS NULL command to check whether a column is null or not.

    if column_name IS NULL then
    ......
    stmt;
    ......
    else
    ......
    stmt;
    ......
    endif;

    U can also use nvl2() function which performs the same functionality described above.

    nvl2(column_name,value1,value2);

    If the column is null it returns value1 else value2.
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Posts
    87

    Talking re:NULL field!

    thanx satish.

    But unfortunately IS NULL command is not working for me.

    I solved my problem using NVL2.

    thanx once again.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140

    Re: re:NULL field!

    Originally posted by raseena
    thanx satish.

    But unfortunately IS NULL command is not working for me.

    I solved my problem using NVL2.

    thanx once again.
    Glad it solved your problem, but how come (just being curious)

    IF COLUMNNAME IS NULL THEN ...

    does not work for you ?

  5. #5
    Join Date
    Nov 2003
    Posts
    87

    how can i use 'IS NULL'

    I tested NVL2 like as follows..

    declare
    catgry varchar2(10);
    begin
    select nvl2(category,'y','n') into catgry from tst where id ='DH9';
    dbms_output.put_line(catgry);
    end;

    here how will i use IS NULL command??

    Thanx.

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: how can i use 'IS NULL'

    Hi Raseena,

    declare
    catgry varchar2(10);
    begin
    select nvl2(category,'y','n') into catgry from tst where id ='DH9';
    dbms_output.put_line(catgry);
    end;

    U r code works fine for a single record. But in case of multiple records?

    In that case U declare a cursor . Fetch the catgry into a variable. Inside a loop, U test whether that variable is null or not, using IS NULL cmmd.
    SATHISH .

  7. #7
    Join Date
    Nov 2003
    Posts
    87

    Red face re:

    OOps! again problem...

    can u rectify this error..
    plz..


    declare
    catgry varchar2(10);
    cursor c1 is
    select category from tst;
    begin
    open c1;
    loop
    fetch c1 into catgry;
    exit when c1%notfound;
    if (IS NULL(catgry)) then
    dbms_output.put_line('Yes');
    else
    dbms_output.put_line('No');
    end if;
    end loop;
    close c1;
    end;
    /

    if (IS NULL(catgry)) then
    *
    ERROR at line 10:
    ORA-06550: line 10, column 5:
    PLS-00103: Encountered the symbol "IS" when expecting one of the following:
    ( - + case mod new not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall merge time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe

  8. #8
    Join Date
    Nov 2003
    Location
    Berlin, Germany
    Posts
    6

    Post Re: re:

    It has to be:
    ...
    if catgry IS NULL then
    ...

  9. #9
    Join Date
    Nov 2003
    Posts
    87

    Talking

    thanx.

    'IS NULL' working!!!!!!!!!!!!!!!!!!!!!!!!

Posting Permissions

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