Re: Need Help : SQL problem

From: Matt B. <mcb_at_ds.znet.com>
Date: Wed, 6 Dec 2000 21:42:21 -0800
Message-ID: <t2u8nfcao2md63@corp.supernews.com>

"Adrian Ting" <adrianting_at_post1.com> wrote in message news:90ki8c$rgs$1_at_violet.singnet.com.sg...
> Hi to all SQL gurus out there,
>
> Current facing a 'simple' SQL problem and need some help from you.
> I am using Oracle 8i Standard ed.
>
> 1) I have a table A.
> 2) I need to retrieve all records from table A which has column X being
> NULL.
> 3) Column X is a number field.
>
> I've heard that in Oracle, NULL comparisons always end up being NULL, any
> other ways to do this?
>
> Thanks.

You heard right, but in your situation it doesn't quite apply.

In a where statement (a delete, update, or select), just use IS NULL:

...
WHERE some_column_name IS NULL
...

That'll work fine.

What you heard about null comparisons is this:

select 'X'
from some_table
where '' = '';

Or this (PL/SQL):

set serveroutput on
DECLARE
   v_x VARCHAR(1) := NULL;
   v_y VARCHAR(1) := NULL;
BEGIN
   IF v_x = v_y THEN

      dbms_output.put_line('They are null.');    END IF;
END; You wouldn't get your dbms_output message. You'd need to do it this way:

set serveroutput on
DECLARE
   v_x VARCHAR(1) := NULL;
   v_y VARCHAR(1) := NULL;
BEGIN
   IF v_x IS NULL AND

      v_y IS NULL THEN
      dbms_output.put_line('They are null.');

   END IF;
END; Get the idea? Direct comparisons will be null when using the relational operator of '=' but not when using the IS NULL keywords.

-Matt Received on Wed Dec 06 2000 - 23:42:21 CST