Home » SQL & PL/SQL » SQL & PL/SQL » Compare Null Value
Compare Null Value [message #293450] Sat, 12 January 2008 23:33 Go to next message
ednms
Messages: 39
Registered: November 2007
Member
Hi. I has sql query to check duplicate data.

SELECT distinct T2.*
FROM BILL_ADJUSTMENT T1, NPCS_INPUT_TXN T2
WHERE T2.npcs_file_seq_num = 26500
AND T1.acct_no=T2.acct_no
AND T1.rev_code=T2.rev_code
AND T1.serv_no=T2.serv_no
AND T1.adj_type_no=T2.adj_type_no
AND T1.pay_receipt_no=T2.pay_receipt_no

Below is the data

[SIZE=1]ACCT_NO        SERV_NO              ADJ_TYPE_NO PAY_RECEIPT_NO           
-------------- -------------------- ----------- ------------------------ 
D905290740106  00362575185          61                                   
D905290740106  00362575185          61                                   
D905290740106  00362575185          61                                   

3 rows selected

ACCT_NO        SERV_NO              ADJ_TYPE_NO PAY_RECEIPT_NO           
-------------- -------------------- ----------- ------------------------ 
D905290740106  00362575185          61                                   

1 rows selected[/SIZE]


I should get 1 row selected when i execute my query but the line
Quote:

AND T1.pay_receipt_no=T2.pay_receipt_no

cause no row selected.
Is it because oracle cannot compare null value?

I try to use
Quote:

AND nvl(T1.pay_receipt_no,'na')=nvl(T2.pay_receipt_no,'na')

Is this the effective way to solve comparing null values between 2 field?

Please Advise.
Re: Compare Null Value [message #293451 is a reply to message #293450] Sat, 12 January 2008 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
NULL <> NULL
Nothing equals NULL
Nothing not equals NULL.
=========================
A IS NULL
A IS NOT NULL
an object either IS NULL or IS NOT NULL

[Updated on: Sat, 12 January 2008 23:37] by Moderator

Report message to a moderator

Re: Compare Null Value [message #293453 is a reply to message #293451] Sun, 13 January 2008 00:00 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
so is that means we cannot just compare 2 field that is nullable?
Re: Compare Null Value [message #293454 is a reply to message #293453] Sun, 13 January 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULL means unknown.
Assume 2 persons answer "I don't know" to your question, do they give the same answer? Does the "I don't know" from the first one the same as the "I don't know" to the second one? Are they different?

Regards
Michel
Re: Compare Null Value [message #293456 is a reply to message #293454] Sun, 13 January 2008 00:37 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
I would say same (is it correct Confused )
So, what is your advice to compare 2 nullable field?
Re: Compare Null Value [message #293457 is a reply to message #293456] Sun, 13 January 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I would say same (is it correct )

No, because it may be "I don't know and I don't care" or "I don't know but this is not my domain" or "I don't know maybe there is an answer but I'm a junior" or "I don't know as there is no answer" or "I don't know as this is a wrong question" or "I don't know ...". There can be an infinitive number of meanings to "I don't know".
So you can't compare 2 "I don't know" unless you force a meaning of this "I don't know", and this is the purpose of NVL.

Regards
Michel
Re: Compare Null Value [message #293458 is a reply to message #293457] Sun, 13 January 2008 01:22 Go to previous message
ednms
Messages: 39
Registered: November 2007
Member
Ok. Smile
Thanks.
Previous Topic: What is the meaning of this statement
Next Topic: Index by clause in collections
Goto Forum:
  


Current Time: Sat Dec 03 13:55:13 CST 2016

Total time taken to generate the page: 0.04558 seconds