Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: NULLS when col is NOT NULL ??

Re: NULLS when col is NOT NULL ??

From: Alan <alan_at_erols.com>
Date: Tue, 24 Aug 2004 15:51:44 -0400
Message-ID: <2p1kihFfumr1U1@uni-berlin.de>

"Ed Stevens" <nospam_at_noway.nohow> wrote in message news:mh4ni096einubbdrd4avi2e4f7pkpv67kd_at_4ax.com...
> Oracle 8.1.7.4.1 on Win2k
>
> Developer brought this to me. Appears to be nulls in a column defined
> as NOT NULL.
>
> Here's what the column looks like:
>
> SQL> desc nlcdb.nlcprc
> Name Null? Type
> ----------------------------------------------------- --------
> ------------------------------------
> <snippage>
>
> NLCPRC_G2_LOAD_NBR NOT NULL
> CHAR(8)
>
> <snippage>
>
> Total number of rows on the table:
> SQL> select count(*) from nlcdb.nlcprc;
>
> COUNT(*)
> ----------
> 92290
>
> 1 row selected.
>
> Total number of rows that are not null on this column (should be all
> of them)
> SQL> edit
> Wrote file afiedt.buf
>
> 1 select count(*)
> 2 from nlcdb.nlcprc
> 3* where nlcprc_g2_load_nbr is not null
> SQL> /
>
> COUNT(*)
> ----------
> 92290
>
> 1 row selected.
>
> So far, so good. Now, what values *do* we have in this column?
> SQL> edit
> Wrote file afiedt.buf
>
> 1 select distinct ascii(nlcprc_g2_load_nbr)
> 2 from nlcdb.nlcprc
> 3* where nlcprc_g2_load_nbr is not null
> SQL> /
>
> ASCII(NLCPRC_G2_LOAD_NBR)
> -------------------------
> 0
> 32
> 76
>
> Huh!?!? What's that acii zero?? Looks like nulls to me! How many of
> those do we have?
>
> SQL> edit
> Wrote file afiedt.buf
>
> 1 select count(*)
> 2 from nlcdb.nlcprc
> 3* where ascii(nlcprc_g2_load_nbr) = 0
> SQL> /
>
> COUNT(*)
> ----------
> 283
>
> 1 row selected.
>
> So, it appears we have 283 instances of nulls (ascii zero) in a column
> defined as NOT NULL. What am I missing here (besides my sanity, of
> course!)

You are missing:

select count(*)
from nlcdb.nlcprc
where nlcprc_g2_load_nbr IS NULL
;

ascii(0) is not the same as NULL. Null means "no value" ascii(0) is a value. Here is some proof:

SQL> select count(*)
  2 from vtl_data
  3 where rate_guarantee is null
  4 ;

  COUNT(*)


         3

  1 select count(*)
  2 from vtl_data
  3* where rate_guarantee = ascii(0)
  4 /

  COUNT(*)


         0 Received on Tue Aug 24 2004 - 14:51:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US