Home » SQL & PL/SQL » SQL & PL/SQL » select query with date = null??
select query with date = null?? [message #193105] Thu, 14 September 2006 22:22 Go to next message
pcgame4u
Messages: 14
Registered: July 2006
Junior Member
My query below is not working, is there any suggestion?

The type of ADATE is DATE

select ADATE from testing where ADATE = NULL;

the query run ok, but it can't select the record that have no value in this column.

Thanks all of you on the help.
Re: select query with date = null?? [message #193108 is a reply to message #193105] Thu, 14 September 2006 22:32 Go to previous messageGo to next message
pcgame4u
Messages: 14
Registered: July 2006
Junior Member
pcgame4u wrote on Thu, 14 September 2006 22:22

My query below is not working, is there any suggestion?

The type of ADATE is DATE

select ADATE from testing where ADATE = NULL;

the query run ok, but it can't select the record that have no value in this column.

Thanks all of you on the help.


My solution below seems to work.

select ADATE from testing
where nvl(TO_CHAR(ADATE),'1') = '1'; 


But is there any solution simplier?
because i force the null as '1' during the selection, is not logical and meaningful i think.
Re: select query with date = null?? [message #193109 is a reply to message #193108] Thu, 14 September 2006 22:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To compare a variable or column to NULL you have to use
IS NULL instead of = NULL
Re: select query with date = null?? [message #193111 is a reply to message #193105] Thu, 14 September 2006 22:36 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
a null is a null, of course of course, and nothing can be compared to a null of course, unless of course the clause is the famous "IS NULL" (kinda lost the tempo at the end there)

read the docs. NULL is UNKNOWN. Unknown is therefore never equal to, or unequal to, anything, including another null. that's why you can use either IS NULL or the NVL function.

returns no rows ever, because where clause never evaluates to TRUE:
select ADATE from testing where ADATE = NULL;
select ADATE from testing where ADATE <> NULL;

returns the rows you want:
select ADATE from testing where ADATE IS NULL;
select ADATE from testing where nvl(ADATE, trunc(sysdate-1000000) ) = trunc(sysdate-1000000);

returns the other rows:
select ADATE from testing where ADATE IS NOT NULL;
select ADATE from testing where nvl(ADATE, trunc(sysdate-1000000) ) <> trunc(sysdate-1000000);
Re: select query with date = null?? [message #193112 is a reply to message #193109] Thu, 14 September 2006 22:36 Go to previous message
pcgame4u
Messages: 14
Registered: July 2006
Junior Member
Frank wrote on Thu, 14 September 2006 22:33

To compare a variable or column to NULL you have to use
IS NULL instead of = NULL

Thanks very much for your reply.
Cool seems to be coding too much java...mix up them.
Previous Topic: Cursor Problem
Next Topic: TOUGH QUERY
Goto Forum:
  


Current Time: Sat Dec 10 10:46:11 CST 2016

Total time taken to generate the page: 0.07557 seconds