How to do both is not null and a not in statement in sql (oracle)?
Clash Royale CLAN TAG#URR8PPP
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.
date_end
to_date
to_date
where date_end not in (229) and date_end is not null
!= 229
not in (229)
The way the month/day is stored is as a number which puts a damper in things. But when I try the first way it says there is a problem with the query. I have looked through the other dates and there is not anything that could be causing a problem
– Rebecca Gonzalez
Aug 8 at 14:24
"it says there is a problem" - please edit your question to show us the actual error you get, and the full query. We can't guess what is wrong, we need to see what you are doing and what happens. The fragment you showed looks OK - here's an example of it working.
– Alex Poole
Aug 8 at 14:25
your date_end is of which datatype??and moreover how are your comparing date_end with 229 if its date ??? post results outcome with tables.
– nikhil sugandh
Aug 9 at 6:32
5 Answers
5
You can try:
where NVL(date_end, 229) != 229
You can simply write:
where date_end <> 229
This also filters out NULL values.
NULL
The same is true for not in:
not in
where date_end not in (229)
If you want to be explicit, use and:
and
where date_end <> 229 and date_end is not null
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));
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
NULL NOT IN ( 229 )
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 |
this has worked for me see if it works for you:
where date_end <>229 and date_end is not null;
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Your first version -
where date_end not in (229) and date_end is not nullshould have worked (though!= 229is better thannot in (229)), at least if the column is a number. What happened when you tried these, and what data are you querying? Sample data and table structures are useful things to include in your question, along with your full query, or a representative version of it if it's big. If 229 is supposed to represent Feb 29th and the column is a date then comparing it with a number doesn't make much sense.– Alex Poole
Aug 8 at 14:16