SELECT statement with NOT and OR in WHERE clause returning incorrect result
561621Feb 13 2007 — edited Feb 14 2007I'm trying to write a select statement using NOT and OR. For some reason, the query is not returning the expected results.
Source Table:
CREATE TABLE NOTTEST (RECID int, F01 char(1), F02 char(1))
Source Data:
RECID F01 F02
1 1 1
2 1 0
3 0 1
4 0 0
5 NULL 0
6 NULL 1
7 0 NULL
8 1 NULL
9 NULL NULL
Select Statement:
SELECT * FROM NOTTEST
WHERE NOT (NVL(F01,'X') = '1' OR NVL(F02,'X') = '1')
On SQL Server, this runs correctly and I get data for records 4, 5, 7 and 9 (replace NVL with COALESCE on SQL). However, when I run this on Oracle 10g (10.1.0.2.0), I get records 3 and 6 as well which do not apply to my WHERE clause.
Can someone please explain why this is happening or offer suggestions as to how I can modify my query to get the expected results?
Thanks in advance.
Mike