SQL & PL/SQL

Hi All,

I'm writing a tutorial on the MODEL clause and, while playing with the PRESENTNNV function, it occurred to me that it appears functionally equivalent to NVL2. Here's a test case.

create table t
( key      number(3)   ,
  val      varchar2(6) ,
  note     varchar2(25)
);

insert into t values ( 1, 'x' , 'val[1] is not null' );
insert into t values ( 2, null, 'val[2] is null'     );

set null "(null)"


select
  key ,
  val ,
  presentnnv_val  ,
  nvl2_val ,
  note
from
  t
model
  dimension by ( key )
  measures
  (
    val ,
    cast ( 'default' as varchar2(15) ) as presentnnv_val ,
    cast ( 'default' as varchar2(15) ) as nvl2_val ,
    note
  )
  rules
  (
    presentnnv_val[ for key in ( 0, 1, 2 ) ] =
      presentnnv( val[cv()], 'expression 1', 'expression 2' ) ,
    nvl2_val[ for key in ( 0, 1, 2 ) ] =
      nvl2( val[cv()], 'expression 1', 'expression 2' ) ,
    note[0] =
      'val[0] is a missing cell'
  )
order by
  key
;

       KEY VAL    PRESENTNNV_VAL  NVL2_VAL        NOTE
---------- ------ --------------- --------------- -------------------------
         0 (null) expression 2    expression 2    val[0] is a missing cell
         1 x      expression 1    expression 1    val[1] is not null
         2 (null) expression 2    expression 2    val[2] is null

drop table t ;

Does anyone know any situations where the two functions would behave differently or where you would want to use PRESENTNNV instead of NVL2?

Thanks in advance for any input you have to offer.

--
Joe Fuda
http://www.sqlsnippets.com/