Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL integer null value or NVL? (Oracle 10gR2, RHEL4)
PL/SQL integer null value or NVL? [message #405766] Fri, 29 May 2009 06:36 Go to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Dear all,

I have observed some function don't work as expected. What's difference between these 2 queries?
I used to think it is same before compare the result.
These 2 queries differ when C148 field null value found.

If somebody know the reason please explain.

select *
FROM T5009 PARTITION (P20090527)
where
(C164 = 'ABC' AND C91 NOT LIKE C21 AND (C84 IS NOT NULL OR C85 IS NOT NULL))
and not (C27 = 320 AND C39 = 13 AND 
nvl(C148, -1)
 = 2 AND C30 = 0 AND C92 NOT LIKE 'RSS')

select *
FROM T5009 PARTITION (P20090527)
where
(C164 = 'ABC' AND C91 NOT LIKE C21 AND (C84 IS NOT NULL OR C85 IS NOT NULL))
and not (C27 = 320 AND C39 = 13 AND 
C148 = 2
AND C30 = 0 AND C92 NOT LIKE 'RSS')


Thanks in advance

[Updated on: Fri, 29 May 2009 06:42]

Report message to a moderator

Re: PL/SQL integer null value or NVL? [message #405769 is a reply to message #405766] Fri, 29 May 2009 06:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I have observed some function don't work as expected.
Perhaps it is you who doesn't understand how this particular function works? Oracle functions usually do what they were designed to. If someone isn't satisfied with that, he/she can write his/her own function and use it instead of the "original" one.

Obviously, these two WHERE clauses aren't the same. Also, NVL(C148, -1) is different from C148 = 2. Therefore, I'd expect them to return different results. So what is your question?
Re: PL/SQL integer null value or NVL? [message #405772 is a reply to message #405766] Fri, 29 May 2009 06:51 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Dear Littlefoot,

Thanks for the quick reply.


nvl(C148, -1) = 2
C148 = 2

Above conditions seem to be same.
These 2 queries differ when C148 field null value found. In other works C148 = 2 condition may dont' work when C148 field has null value. C148 field type is number.



Re: PL/SQL integer null value or NVL? [message #405773 is a reply to message #405772] Fri, 29 May 2009 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not that I don't believe you, but claims like this require at least some evidence.

Can you run these two queries in SQL*Plus and cut and paste the queries and their results back here:
select count(*)
FROM T5009 PARTITION (P20090527)
where C164 = 'ABC' 
AND   C91 NOT LIKE C21 
AND  (C84 IS NOT NULL OR C85 IS NOT NULL)
and  not (C27 = 320 
      AND C39 = 13 
      AND nvl(C148, -1) = 2 
      AND C30 = 0 
      AND C92 NOT LIKE 'RSS')

select count(*)
FROM T5009 PARTITION (P20090527)
where C164 = 'ABC' 
AND   C91 NOT LIKE C21 
AND  (C84 IS NOT NULL OR C85 IS NOT NULL)
and  not (C27 = 320 
      AND C39 = 13 
      AND C148 = 2 
      AND C30 = 0 
      AND C92 NOT LIKE 'RSS')
Re: PL/SQL integer null value or NVL? [message #405775 is a reply to message #405773] Fri, 29 May 2009 07:13 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

select count(*)
FROM T5009 PARTITION (P20090527)
where C164 = 'ABC' 
AND   C91 NOT LIKE C21 
AND  (C84 IS NOT NULL OR C85 IS NOT NULL)
and  not (C27 = 320 
      AND C39 = 13 
      AND nvl(C148, -1) = 2 
      AND C30 = 0 
      AND C92 NOT LIKE 'RSS')


203876
select count(*)
FROM T5009 PARTITION (P20090527)
where C164 = 'ABC' 
AND   C91 NOT LIKE C21 
AND  (C84 IS NOT NULL OR C85 IS NOT NULL)
and  not (C27 = 320 
      AND C39 = 13 
      AND C148 = 2 
      AND C30 = 0 
      AND C92 NOT LIKE 'RSS')


203674
Re: PL/SQL integer null value or NVL? [message #405778 is a reply to message #405772] Fri, 29 May 2009 07:18 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
If c148 is null in your query you would get either
-1 = 2 (for your first query)
or
null = 2 (for your second query)

The two statements are evaluated differently. When you evaluate null = 2 the answer you get back is "unknown", not false. And not(unknown) will also yield unknown. This is why your second query won't find anything if c148 is null.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements005.htm
Re: PL/SQL integer null value or NVL? [message #405781 is a reply to message #405778] Fri, 29 May 2009 07:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. You can't use "not" to turn around "unknown".

Example:

SQL>
SQL> SELECT * FROM (
  2     SELECT 'somevalue' col1,
  3            NULL        col2 FROM dual
  4  ) WHERE  ( Nvl(col2,-1) = 2 );

no rows selected

SQL>
SQL> SELECT * FROM (
  2     SELECT 'somevalue' col1,
  3            NULL        col2 FROM dual
  4  ) WHERE  ( col2 = 2 );

no rows selected

SQL>
SQL> SELECT * FROM (
  2     SELECT 'somevalue' col1,
  3            NULL        col2 FROM dual
  4  ) WHERE NOT ( Nvl(col2,-1) = 2 );

COL1      C
--------- -
somevalue

SQL>
SQL> SELECT * FROM (
  2     SELECT 'somevalue' col1,
  3            NULL        col2 FROM dual
  4  ) WHERE NOT ( col2 = 2 );

no rows selected

Re: PL/SQL integer null value or NVL? [message #405782 is a reply to message #405778] Fri, 29 May 2009 07:24 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Dear c_stenersen,

Thank you very much Smile

Have a nice day
Re: PL/SQL integer null value or NVL? [message #405785 is a reply to message #405775] Fri, 29 May 2009 07:40 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Which part of 'Cut and Paste from Sql*Plus' was causing you problems?

Thinking about it, this is correct, but you need to understand Nulls and 3 value boolean logic.

Null is best thought of as 'Unknown' for this purpose.

Any comparison test involving a null returns null, so 'col148 > 5' returns null if col148 is null, whereas 'nvl(col148,-1) > 5' returns false if col148 is null - if you think of null as unknown, this makes sense, as the answer to 'is this unknown value > 5' is unknown.

AND comparisons with nulls work like this:

True AND null => null
False AND null => False

What's happening is that rows where C148 is null are returning a TRUE/FALSE for the version of the query with NVL in it, and are returning NULL for the version with the straight comparison.
The rows returning NULL are failing the NOT () check (as Null fails all tests) and are being excluded from the count.

You will get a more intuitive response if you rewrite your NOT (A and B and C) as (not(A) or not(B) or not(C))

ie
and  (C27 != 320 
      OR C39 != 13 
      OR C148 != 2 
      OR C30 != 0 
      OR C92 LIKE 'RSS')


(ps - LIKE terms without wildcards in them are just = statements that slow the optimiser down)
Previous Topic: how to call ALTER from procedure
Next Topic: Tuning
Goto Forum:
  


Current Time: Sun Dec 11 02:24:02 CST 2016

Total time taken to generate the page: 0.09112 seconds