CODEWITHSUNDEEP
sqloracle-database
Rebecca Gonzalez
Rebecca Gonzalez

Reputation: 53

How to do both is not null and a not in statement in sql (oracle)?

I am trying to say the statement

where date_end not in (null, 229)

I have already tried 

where date_end not in (229) and date_end is not null

I have also tried 

where date_end is not null or date_end not (229)

This is necessary because I am using the date_end in a to_date statement to create a date. I want to ignore 229 because of leap years and nulls because it will not respond in the to_date statement.

Upvotes: 3

Views: 70

Answers (5)

Nikhil
Nikhil

Reputation: 3950

this has worked for me see if it works for you:

where date_end <>229 and date_end  is not null;

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

Just do:

where date_end not in ( 229 )

NULL NOT IN ( 229 ) always evaluates to NULL, which is equivalent to FALSE in WHERE clause. Additional NOT NULL checking is needless.
Please examine a simple demo: http://www.sqlfiddle.com/#!4/358b6e/2

CREATE table test(
  date_end int
);

INSERT ALL
INTO test VALUES( null )
INTO test VALUES( 229 )
INTO test VALUES( 123 )
SELECT null FROM dual;

SELECT * FROM test;

| DATE_END |
|----------|
|   (null) |
|      229 |
|      123 |

SELECT * FROM test
WHERE date_end NOT IN ( 229 )

| DATE_END |
|----------|
|      123 |

Upvotes: 0

Bhanu Yadav
Bhanu Yadav

Reputation: 169

This will be a better option in case there are other non-null values than 229.

(    where date_end is  null and  date_end  not in (229));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can simply write:

where date_end <> 229

This also filters out NULL values.

The same is true for not in:

where date_end not in (229)

If you want to be explicit, use and:

where date_end <> 229 and date_end is not null

Upvotes: 3

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

You can try:

where NVL(date_end, 229) != 229

Upvotes: 4

Related Questions