NULLS when col is NOT NULL ??

From: Ed Stevens <nospam_at_noway.nohow>
Date: Tue, 24 Aug 2004 14:21:00 -0500
Message-ID: <mh4ni096einubbdrd4avi2e4f7pkpv67kd@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!) Received on Tue Aug 24 2004 - 14:21:00 CDT