Re: NOT IN (not working for me !)

Rob Weeks wrote:
>
> Hi guys...
>
> Pardon me if this is a newbie type question but......I have a view that
> is in our DB2 world that I am attempting to get to work in Oracle. If I
> run a query based on that view using the same data in both environments,
> I am getting very different answers. Here is a part of the SQL from the
> view that I believe may be causing the problem:
>
> ... WHERE REASON_CODE NOT IN ('398',498')
>
> Lets say that the base table's name is sales and the new view is
> named sales_v. If I run "SELECT COUNT(*) FROM SALES WHERE REASON_CODE
> NOT IN ('398','498'", on both systems I get radically different answers.
> If I run the same query without the "NOT", I get the same answer on
> both systems.
>
> I suspect that this has something to do with Nulls in the reason_code
> field, but at this point I'm not sure of anything. Does anyone know how
> I can fix this?
>
> Thanks in advance
> Rob Weeks
> rweeks_at_kodak.com
> rweeks_at_hotliquid.com

You are correct, Oracle will not count NULLs in that field. You can use the nvl function to get around this... Received on Sat Jul 13 1996 - 00:00:00 CEST