OraFAQ Forum: SQL & PL/SQL » select query with date = null??

Home » SQL & PL/SQL » SQL & PL/SQL » select query with date = null??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select query with date = null?? [message #193105]

Thu, 14 September 2006 22:22

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.

Report message to a moderator

Re: select query with date = null?? [message #193108 is a reply to message #193105]

Thu, 14 September 2006 22:32

pcgame4u
Messages: 14
Registered: July 2006

Junior Member

pcgame4u wrote on Thu, 14 September 2006 22:22

My solution below seems to work.

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: select query with date = null?? [message #193109 is a reply to message #193108]

Thu, 14 September 2006 22:33

Frank
Messages: 7901
Registered: March 2000

Senior Member

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

Report message to a moderator

Re: select query with date = null?? [message #193111 is a reply to message #193105]

Thu, 14 September 2006 22:36

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);

Report message to a moderator

Re: select query with date = null?? [message #193112 is a reply to message #193109]

Thu, 14 September 2006 22:36

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.

Report message to a moderator

Previous Topic:	Cursor Problem
Next Topic:	TOUGH QUERY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 29 06:48:45 CDT 2025