Re: Problem with result set from a simple SQL Select

lenny (lwintfeld_at_libertycorner.net) wrote:
: Hi

: I'm making the following select in an Oracle 9i database:

: select s.cusip, s.udf3, s.udf2 from trade t, security s where t.sec_sid
: = s.sec_sid and s.udf2 != 'CMO' and end_date >= '25-jan-05';

: As expected, this does exclude records with values of 'CMO' in s.udf2
: from my result set but for some reason it is ALSO excluding records
: with the value NULL/empty (I'm not sure which)in field s.udf2.

Yes, that's the way it works.

By comparing the column with a value you are requesting the database to provide rows where the comparison is known to be true. In this case you are requesting rows in which the column is known to have a value other than 'CMO'.

However, the database does not know the value of the data when it has not been entered into the column, and therefore cannot select that row.

This might sound backwards to you, and if you designed the database then you might choose to do this differently, but you didn't - so instead you must simply understand the reasoning and live with it.

SO HOW TO SELECT THE NULLS you ask

simple, just include the fact that nulls are ok in the comparison.

e.g.1

• note brackets to make sure the two tests are "together"

        where ( col != 'CMO' OR col is null)

e.g.2

• NVL is an oracle function, RTM

        where nvl(col,'this_isnt_CMO') != 'CMO'         

NOTE the null column behaviour can be caused from anywhere in a statement, including places like an order by.

The rule is to check what columns can be null and make sure you handle every use of each such column.

In general there is no 100% consistent way to mix nulls and data, which is the ultimate reason why the database forces you to handle this yourself.

--

This space not for rent.