Home » SQL & PL/SQL » SQL & PL/SQL » IS NULL and ='NULL' (Oracle10.2.0.3)
IS NULL and ='NULL' [message #413062] Tue, 14 July 2009 04:34 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
 
In 10g
SQL1:
SELECT * 
FROM test t 
WHERE (RTRIM(UPPER(in_last_nme)) IS NULL 
OR (RTRIM(UPPER(t.last_nme)) LIKE RTRIM(UPPER(in_last_nme))||'%') 
OR (RTRIM(UPPER(in_last_nme)) IS NULL 
AND RTRIM(UPPER(t.last_nme)) IS NULL))




SQL2: In 9i

SELECT * 
FROM test t 
WHERE (RTRIM(UPPER(in_last_nme)) IS NULL 
OR (RTRIM(UPPER(t.last_nme)) LIKE RTRIM(UPPER(in_last_nme))||'%') 
OR (RTRIM(UPPER(in_last_nme)) ='NULL' 
AND RTRIM(UPPER(t.last_nme)) IS NULL))


In 9i database its working fine but in 10g when we passed the last_nme I am not getting result.

I also understand the last line in the query is not required.

Are
(RTRIM(UPPER(in_last_nme)) ='NULL' treated differently in 9i and 10g?
Thanks
Oli
Re: IS NULL and ='NULL' [message #413068 is a reply to message #413062] Tue, 14 July 2009 04:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
"IS NULL" is to check the equality with null.
"= NULL" doesn't make any sense. That is wrong.
"='NULL'" here you are comparing 'NULL' as a string. It is not NULL. It is a 4 character word.

Also, both the queries you have mentioned are not equivalent.
One is having "IS NULL" and the other "='NULL'".

By
Vamsi
Re: IS NULL and ='NULL' [message #413095 is a reply to message #413062] Tue, 14 July 2009 06:20 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
in_last_nme is the IN parameter defined in the procedure and DEFAULT NULL

Create or replace procedure test0707
( in_last_nme IN VARCHAR2(20) DEFAULT NULL)
AS
....


when no values are passed can it be (RTRIM(UPPER(in_last_nme)) ='NULL' ?



Re: IS NULL and ='NULL' [message #413096 is a reply to message #413095] Tue, 14 July 2009 06:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
'NULL' is a 4 character string.
NULL is the absence of a value.

'NULL' is NOT the same as 'NULL'
Re: IS NULL and ='NULL' [message #413098 is a reply to message #413096] Tue, 14 July 2009 06:36 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks
Re: IS NULL and ='NULL' [message #413104 is a reply to message #413062] Tue, 14 July 2009 06:55 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Just to add this Post...

Its also that Null=Null is FALSE not True !!

DECLARE 
  y  NUMBER; 
   x  NUMBER; 
BEGIN 
  y := 5; 
   
  x := NULL; 
   
  IF x = NULL THEN 
    y := 1; 
  ELSE 
    y := 2; 
  END IF; 
   
  dbms_output.Put_line(y); 
END; 
Re: IS NULL and ='NULL' [message #413108 is a reply to message #413104] Tue, 14 July 2009 07:07 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
Its also that Null=Null is FALSE not True !!


Null compared with any other value is neither true nor false it is simply Null or unknown.

change your code like this

DECLARE 
  y  NUMBER; 
   x  NUMBER; 
BEGIN 
  y := 5; 
   
  x := NULL; 
   
  IF x != NULL THEN 
    y := 1; 
  ELSE 
    y := 2; 
  END IF; 
   
  dbms_output.Put_line(y); 
END; 








Re: IS NULL and ='NULL' [message #413111 is a reply to message #413108] Tue, 14 July 2009 07:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Agreed. i would be more inclined to demonstrate it thus:
DECLARE 
   x  NUMBER; 
BEGIN 
  x := NULL; 
  if (x=null) = true then 
   dbms_output.Put_line('True'); 
  elsif (x=null) = false then 
    dbms_output.Put_line('False'); 
  elsif (x=null) is null then 
  dbms_output.Put_line('null');
  end if;
  if (x!=null) = true then 
   dbms_output.Put_line('True'); 
  elsif (x!=null) = false then 
    dbms_output.Put_line('False'); 
  elsif (x!=null) is null then 
  dbms_output.Put_line('null');
  end if;
END; 
Re: IS NULL and ='NULL' [message #413112 is a reply to message #413062] Tue, 14 July 2009 07:23 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey Guyz,

thanks for correcting it and giving a elaborated example Smile
Re: IS NULL and ='NULL' [message #413114 is a reply to message #413062] Tue, 14 July 2009 07:34 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Moreover,
As One NULL value doesn't match with other NULL value, we can insert multiple NULLs in UNIQUE column.

regards,
Delna
Re: IS NULL and ='NULL' [message #413125 is a reply to message #413114] Tue, 14 July 2009 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Tue, 14 July 2009 14:34
Moreover,
As One NULL value doesn't match with other NULL value, we can insert multiple NULLs in UNIQUE column.

regards,
Delna

Not fully true.
SQL> create table t (col1 integer, col2 integer, unique(col1,col2));

Table created.

SQL> insert into t values (1,null);

1 row created.

SQL> /
insert into t values (1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.SYS_C006279) violated

For unique constraint, ALL columns must be null to not raise unique violation:
SQL> insert into t values (null, null);

1 row created.

SQL> /

1 row created.

Regards
Michel

[Updated on: Tue, 14 July 2009 08:57]

Report message to a moderator

Re: IS NULL and ='NULL' [message #413134 is a reply to message #413125] Tue, 14 July 2009 09:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hmmm, although I agree that
Quote:
For unique constraint, ALL columns must be null to not raise unique violation:

Delna said:
Quote:
we can insert multiple NULLs in UNIQUE column.
i.e. she said nothing about a unique constraint on multiple columns and therefore her statement is fully true, it's just that yours is an expansion of the point that she was making.
Re: IS NULL and ='NULL' [message #413488 is a reply to message #413062] Thu, 16 July 2009 01:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you Michel sir for being more clear.
But pablolee sir understood me correctly.

regards,
Delna
Re: IS NULL and ='NULL' [message #413543 is a reply to message #413488] Thu, 16 July 2009 05:39 Go to previous messageGo to next message
mohan_krishnan83
Messages: 10
Registered: July 2006
Junior Member
As Delna told,
We can insert multiple Null values on unique columns
Oracle will not throw any error.

CREATE TABLE dummy157 (
col1 NUMBER)

CREATE UNIQUE INDEX u_col1_dummy157
ON dummy157(col1)

INSERT INTO dummy157
VALUES (1);

INSERT INTO dummy157
VALUES (NULL);

INSERT INTO dummy157
VALUES (NULL);

Oracle won't thhrow any error.

But SQLserver accepts only one Null value



Re: IS NULL and ='NULL' [message #413546 is a reply to message #413543] Thu, 16 July 2009 05:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That really isn't our problem.
Re: IS NULL and ='NULL' [message #413576 is a reply to message #413108] Thu, 16 July 2009 08:41 Go to previous messageGo to next message
ganludong
Messages: 5
Registered: July 2009
Location: SICHUAN NORMAL UNIVERSITY...
Junior Member
'NULL' is stand for four characters
'' is stand for a space character
NULL is stand for empty nothing
Re: IS NULL and ='NULL' [message #413580 is a reply to message #413576] Thu, 16 July 2009 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for (badly) repeating what has already been said.
Could you do it once more?

Regards
Michel

[Updated on: Thu, 16 July 2009 08:59]

Report message to a moderator

Re: IS NULL and ='NULL' [message #413582 is a reply to message #413576] Thu, 16 July 2009 08:58 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
ganludong wrote on Thu, 16 July 2009 14:41
'NULL' is stand for four characters
'' is stand for a space character
NULL is stand for empty nothing


'' is null unless you're using char datatypes in which case it is space (confusingly).

' ' is space (obviously).
Previous Topic: Insufficient Privilege
Next Topic: ORA-02437
Goto Forum:
  


Current Time: Mon Dec 05 18:59:56 CST 2016

Total time taken to generate the page: 0.09856 seconds