Results 1 to 2 of 2

Thread: NVL function

  1. #1
    Join Date
    May 2005

    Question Unanswered: NVL function

    I am using a debug tool to step through my code and have noticed that the NVL function evaluates both it's arguments before returning a value. My code reads

    nvl(dept_link_f(p_sid, p_link_id), state_get_f(p_sid, 'DEPT_CODE'))

    Since the dept_link_f function returns a value, there is no need for the state_get_f function to be executed.

    Would I be better off using IF statements instead of NVL or am I missing something?

    Thanks in advance

  2. #2
    Join Date
    Oct 2002
    Cape Town, South Africa
    The reason for this is that it executes NVL exactly like it executes your own functions... It has to execute the inner functions in order to receive it's inputs into the NVL function.

    Would I be better off using IF statements instead of NVL or am I missing something?
    This depends on where you are using the NVL function.... If you are using NVL in a sql select eg.
       nvl(dept_link_f(p_sid, p_link_id), state_get_f(p_sid, 'DEPT_CODE')), col_list... 
    from table_list... where criteria...
    then you could use the CASE statement. The above statmenet would look as follows:
       case when dept_link_f(p_sid, p_link_id) is null then state_get_f(p_sid, 'DEPT_CODE')
       else dept_link_f(p_sid, p_link_id)
       end case
    , col_list...
    from table_list... where criteria...
    Upside would be that state_get_f would not execute unless the other function returns null. The downside is that dept_link_f will run at lease once, and twice if it returns anything but null.

    If you using NVL in PL/SQL, then you can write this more efficiently using the following:
    ret_val varchar2(1024); -- assuming the dept_link_f function returns varchar2
       ret_val := dept_link_f(p_sid, p_link_id);
       if ret_val is null then
         ret_val := state_get_f(p_sid, 'DEPT_CODE');
       end if;
       -- use ret_val for whatever
    This makes sure each of the two functions only execute once.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts