CODEWITHSUNDEEP
oracle-database
user3171798
user3171798

Reputation: 1

Null comparison is not happening to the fields in where clause

Null comparison is not happening to the fields in where clause.
Code is as below. Its just a sample code where null comparison is not happening.

create or replace function document_history
    (V_process_id IN) 
    return varchar2 
AS   
    V_document_id number;         
    V_process_status varchar2(10);   

select document_id, process_status 
into V_document_id, V_process_status 
from document_details 
where process_id =V_process_id ;  

update staging2 
set (country,land) = (select country, land 
                      from staging 
                      where document_id =V_document_id);  
commit;  

return null;  
end document_history;

Here if input varible V_document_id is null, sub query will not return any value where I'm expecting the output for null value also.

I tried below scenarios to get output for the subquery:

  1. select country, land from staging where nvl(document_id,'*') = nvl(V_document_id,'*');

  2. select country, land from staging where document_id = nvl(V_document_id,null);

  3. I declared one variable with null value and used in the nvl function,
    nl_variable:=null; select country, land from staging where document_id = nvl(V_document_id,nl_variable);

  4. I tried trim(V_document_id).

Only for 1st option, query would return the value.

Wherein, my real procedure/functions are big and they have many columns in where clause which needs to return the value if input is null and I cant use nvl(input,'*') on all the columns in where clause and also cant use IS NULL (as comparison should be like (document_id =V_document_id)).

Could someone help me, how null comparison can be done here?

Upvotes: 0

Views: 53

Answers (1)

Vasyl Moskalov
Vasyl Moskalov

Reputation: 4650

Looks like I can purpose a solution. This solution, in fact, is a hack

Just replace 

document_id =V_document_id

with

decode(document_id,V_document_id,1,0)=1

From Oracle documentation

However, Oracle considers two nulls to be equal when evaluating a DECODE function. Please refer to DECODE for syntax and additional information.

Upvotes: 2

Related Questions