Home » SQL & PL/SQL » SQL & PL/SQL » not sure what the nvl() function is replacing here...
not sure what the nvl() function is replacing here... [message #424212] Wed, 30 September 2009 14:12 Go to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
Hi all- I was hoping to get a bit of help; I've been tasked with cleaning up someone else's (probably poorly) written code.

What I'm trying to figure out is exactly what task the nvl() function is performing here in this code snippet:


declare
    cs NUMBER := 0;
    r2 owner.charge_fact%ROWTYPE;
    cursor c1 is select * from really_bad_inline_select_statement;
  begin
    for r1 in c1 loop
     cs := cs+1;
      select * into r2 from owner.charge_fact r0 for update;
      
       if nvl(r1.facility_ptr=r2.facility_ptr,r1.facility_ptr is NULL and r2.facility_ptr is NULL) then
        r1.facility_dim := r2.facility_dim;
       end if;
      
  -- <...Then, this *SAME* nvl structure above repeats several times here, only using different columns from r1 and r2.>

  -- <...Then, it goes:> 
 
     if not (
     nvl(r1.dataset_dim=r2.dataset_dim,r1.dataset_dim is NULL and r2.dataset_dim is NULL) and
     nvl(r1.dataset_code=r2.dataset_code,r1.dataset_code is NULL and r2.dataset_code is NULL)
     ) then
     update owner.charge_fact r0 set
      r0.expired=rundt-1
     where
      r0.charge_fact=r1.charge_fact

     end loop;
  end;
  /



...So- as far as I know, nvl() is used to replace one NULL thing with a real value.

But in this case, it almost seems like its saying something to the effect of:

"...If r1.column equals r2.column, and that resulting value happens to be NULL, then replace it with r2.column, provided both of those columns are null."

Hunh?? ...Is that what nvl() is doing, or is it doing something else?? ...I'm actually baffled that this supposedly "works".
(FYI- the table charge_fact also has a column named charge_fact.)

Any help would be greatly appreciated.


.
Re: not sure what the nvl() function is replacing here... [message #424217 is a reply to message #424212] Wed, 30 September 2009 15:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The way I read it. (pseudo - Code)

nvl(
  r1.facility_ptr=r2.facility_ptr --> 
      --  would be TRUE if equal, FALSE if un-equal and NULL
      --  if one of them is null.
  ,r1.facility_ptr is NULL and r2.facility_ptr is NULL -->
      -- Is TRUE if both are NULL, false if only one of them
      -- Is NULL
)


So it seems to be used as an "is equal" check that also returns true/false if one of the both is null, which would otherwise return NULL, a.k.a. "undefined/unknown"
Re: not sure what the nvl() function is replacing here... [message #424224 is a reply to message #424212] Wed, 30 September 2009 20:27 Go to previous messageGo to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
Ok- so then assume the first part returns TRUE (both columns are equal), what value does the second part return?

Like for example:
(pseudo-code)

IF 
 nvl(both_columns_are_equal, returns_true_because_both_columns_are_null)
THEN
 make_column_1_equal_column_2
END IF;


Right?

...Uhhh- but we already knew column 1 was equal to column 2, because that's what we tested for in the nvl expression. So, in that case, wouldn't column 1 just get set to NULL anyway??

...I'm trying to figure out some way to test this to see what it outputs- like perhaps using DBMS_OUTPUT.PUT_LINE?

It just seems to me that there's got to be a much easier (and faster) way to do this. You know- one that doesn't do this consecutive series of nvl tests for each row the cursor looks at (it does this for over 1.2 million rows).
...Like for example, maybe use an update statement to populate a temporary table with the correct values for column 1, and get rid of the cursor??

[Updated on: Wed, 30 September 2009 20:32]

Report message to a moderator

Re: not sure what the nvl() function is replacing here... [message #424260 is a reply to message #424224] Thu, 01 October 2009 03:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you're misreading the code. A better pseudo code would be:
IF 
 nvl(record_1_column_1_equals_record_2_column_1, returns_true_because_both_columns_are_null)
THEN
 make_record_1_column_1_equal_record_2_column_1
END IF;
- ie the columns being compared aren't the same as the columns being set equal to each other.

I'd say that this is definitely more confusing than
if (r1.facility_ptr=r2.facility_ptr or r1.facility_ptr is NULL and r2.facility_ptr is NULL) then
        r1.facility_dim := r2.facility_dim;
       end if;
Previous Topic: NLS Character Issue
Next Topic: Right Text Alignment
Goto Forum:
  


Current Time: Wed Dec 07 08:41:13 CST 2016

Total time taken to generate the page: 0.15658 seconds