Re: NOT IN (not working for me !)

Rob Weeks <rweeks_at_hotliquid.com> 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

I've not worked with DB2, but NULLS would be my first guess. Some databases treat NULL like an empty string. In ORACLE, nulls are treated for the most part as an unknown value. Is "unknown" in ('398','498'). Who knows? It might be, but then again it might not be! It's unknown.

Nulls can be a pain when new to Oracle. In essence, comparison's no longer have just two results - true or false; they have three - true, false, or null.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!