Re: sql question...

Igor Keselman wrote:
>
> oispeggy_at_acsu.buffalo.edu wrote:
> >
> > Learn something new every day....
> >
> > 1.
> > select lname, fname This returns everyone with 'N' or 'G' or ' '.
> > from resdemo None with 'Y'. None with null.
> > where fellow != 'Y'
> >
> >>
> > What surprised me is that #1 did not return anyone with a null value?
> > I thought it would? Why didn't it?
>
> Because it's supposed to select rows that "make" (fellow!='Y') TRUE.
> But, (null!='Y') is not TRUE (it's not FALSE either go figure :-) )
>
> In your case you could probably do something like:
> select lname, fname
> from resdemo
> where nvl(fellow,'N')!='Y'
>
> Hope it helps,
>
> Igor

Igor is quite right, because the NVL function gives a value to a non-value, making it accessible to comparison. NULL is one of the harder "ideas" in learning SQL. Note also if you attempt to join a column which can contain null values, rows with nulls in the column do not make it into the join, even tho other values in the row do join. Before the days of NVL, one of the greatest inventions of ORACLE's PL/SQL, about the only thing you can do with columns that can contain nulls was to test IS NULL or IS NOT NULL.

Happy NULLING!

regards,

john Received on Thu Jun 12 1997 - 00:00:00 CEST