NULL values being excluded

[Matt Molis]

IM 10.5.1
Oracle 11g

I am running the following SQL:

select *
from shars.program
where prog_name = 'LIHC' and lihc_credit_stat <> 1

lihc_credit_stat is a NUMBER(1,0) column type that contains (0, 1, 2, 3 or
NULL)

The problem I'm having is that not only are rows containing lihc_credit_stat
= 1 being eliminated, but also the rows where lihc_credit_stat is NULL.

I need for the rows where lihc_credit_stat is NULL to be in the resultset
without having to add an additional clause of "or lihc_credit_stat is NULL".

Does anyone have some insight into this?

Thanks,
Matt Molis
NYS DHCR

[Terry Dykstra]

Because NULL is undefined, you cannot compare anything to null. So
change your query to:

select *
from shars.program
where prog_name = 'LIHC' and nvl(lihc_credit_stat,0) <> 1


The nvl() allows you to substitute a value for the null.

Terry Dykstra [TeamSybase]

Post by Matt Molis
IM 10.5.1
Oracle 11g
select *
from shars.program
where prog_name = 'LIHC' and lihc_credit_stat <> 1
lihc_credit_stat is a NUMBER(1,0) column type that contains (0, 1, 2, 3 or
NULL)
The problem I'm having is that not only are rows containing lihc_credit_stat
= 1 being eliminated, but also the rows where lihc_credit_stat is NULL.
I need for the rows where lihc_credit_stat is NULL to be in the resultset
without having to add an additional clause of "or lihc_credit_stat is NULL".
Does anyone have some insight into this?
Thanks,
Matt Molis
NYS DHCR