asked on

Oracle query nvl issue

 IF (v_old_loc_id_stored is not NULL OR v_loc_id is not NULL) THEN  

               v_whse := GET_WHMNT_ID(nvl(v_old_loc_id_stored,v_loc_id), v_div_no); 
End If;

Open in new window

If either of the variables is not null then I get warehouse using the get_whmnt_id function.
I think nvl is not correct. My problem is if v_old_loc_id_stored is null my nvl will work. How to handle if v_loc_id is null?

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

So my code is correct? If either one is null will it handle?

slightwv (䄆 Netminder)

To get to GET_WHMNT_ID one or both must have a value. If v_loc_id is null then v_old_loc_id_stored cannot.

So, the NVL will always return a value.

anumoses

ASKER

Also is there a need for nvl? In my case its OR
v_old_loc_id_stored is not null or v_loc_id is not null

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

what happens if 2nd value is null. That is the confusion for me. In my case, anyone could be null. So how to derive my warehouse by using my function so that my warehouse id is not null based on loc id.

anumoses

ASKER

My requirement is that to check v_old_loc_id_stored and v_loc_id

if either one is not null proceed to the next step, else exception no_data_found.

anumoses

ASKER

Your first answer

Do you mean: "How to handle it if both v_loc_id and v_old_loc_id are null?

Your logic should work fine if either of these fields is null, as long as the other one has a value. If both of these can be null, then using NVL with those two fields will not help you.

NO if both are null then I raise exception no_data_found and write to error table.

Your logic should work fine if either of these fields is null, as long as the other one has a value.

So I think my code will work. Will do some testing.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

You might try using the coalesce function which takes the first non-null value available.
v_whse := get_whse(coalsesce(v_old_loc_id_stored, v_loc_id, v_default) ==> where v_default could be a value you choose when the first two arguments are both null)

Then you could add if v_whse = v_default raise exception

awking00

I don't want to confuse the issue, but you would still need the v_div_no parameter for the get_whse function.

anumoses

ASKER

thanks