Could there be a difference from a performance point of view?

I was unpleasantly surprised to find out that in the 
call to NVL(expr1, expr2), expr2 seems to always be 
evaluated. We were using NVL() in our nightly load to 
the warehouse like this:

   insert into table
    select ... 
           NVL(field_x, expensive_function()),

field_x is very rarely NULL, but when it is, we need to
correct it by calling a somewhat 'heavy' function.

Here is a test I did to validate what I think is happening:

SQL> create or replace function sleep_now return number  is
 i number;
 begin
   i :=0;
   while i < 1000000
   loop
      i := i + 1;
   end loop;
   return i;
 end;


SQL> create table t (i int);

SQL> insert into t values (1);
SQL> insert into t values (1);
SQL> insert into t values (1);
SQL> commit;

SQL> set timing on

SQL> select * from t;

         I
----------
         1
         1
         1

Elapsed: 00:00:00.01


SQL> select nvl(i,sleep_now()) from t; 

NVL(I,SLEEP_NOW_FNN())
----------------------
                     1
                     1
                     1

Elapsed: 00:00:07.58       <==============


It still happens even with a constant!!

SQL> select nvl(9,sleep_now()) from t; 

NVL(9,SLEEP_NOW())
------------------
                 9
                 9
                 9

Elapsed: 00:00:08.27   <===============


But decode doesn't seem to have the same problem:


SQL> select decode(i , NULL, sleep_now(), 9) from t;

DECODE(I,NULL,SLEEP_NOW(),9)
----------------------------
                           9
                           9
                           9

Elapsed: 00:00:00.00

SQL> select decode(i , 1, sleep_now(), 9) from t;

DECODE(I,1,SLEEP_NOW(),9)
-------------------------
                  1000000
                  1000000
                  1000000

Elapsed: 00:00:09.03


Am I missing something? Does Nvl() always exaluate expr2?
If so, why?


Steve
 

Dear Tom,
Can coalesce function be used instead of nvl()?Do you find
any performance trade off there?
Please do reply.

Is it not the same issue?I was thinking the to_date in nvl functions is making the difference and if I remove the date comparisions the results would be comparable. But it is not.

SQL>create or replace
  2  procedure P1 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  3    x number;
  4  begin
  5    if a != b or ( a is null and b is not null ) or 
  6       ( a is not null and b is null ) then x := 1; end if;
  7  /*
  8    if c != d or ( c is null and d is not null ) or 
  9       ( c is not null and d is null ) then x := 1; end if;
 10    if e != f or ( e is null and f is not null ) or 
 11       ( e is not null and f is null ) then x := 1; end if;
 12  */
 13  end;
 14  /

Procedure created.

Elapsed: 00:00:00.01
SQL>create or replace
  2  procedure P2 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  3    x number;
  4  begin
  5    if nvl(a,-1) != nvl(b,-1) then x := 1; end if;
  6  /*
  7    if nvl(c,to_date('0001','yyyy')) != nvl(d,to_date('0001','yyyy')) then x := 1; end if;
  8    if nvl(e,' ') != nvl(f,' ' ) then x := 1; end if;
  9  */
 10  end;
 11  /

Procedure created.

Elapsed: 00:00:00.01
SQL>declare
  2    d date := sysdate;
  3    v varchar2(10) := '123123';
  4  begin
  5  for i in 1 .. 1000000 loop
  6    p1(i,i,d,d,v,v);
  7  --  p1(null,null,null,null,null,null);
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.05
SQL>declare
  2    d date := sysdate;
  3    v varchar2(10) := '123123';
  4  begin
  5  for i in 1 .. 1000000 loop
  6    p2(i,i,d,d,v,v);
  7  --  p2(null,null,null,null,null,null);
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:09.01
SQL>
 

what will happened when we give rollback
whether it will be in logfile
whether it will generate scn
whether it will generate checkpoint
what all things will be happened in background
plz let me know

COALESCE() is nice to replace nested NVLs.

Suppose I have

coalesce(col1,col2,col3)

In addition to getting the first non-null value, is there a way I can "know" which column it came from (col1 or col2 or col3 in the above case)?

Thanks

Tom,

For example I have a,b,c let's say tables tbla
and d from tblb. This is the way I have it right now
in order to get CH from another table.

SELECT DECODE(1, '1', 'CN', 'UNKNOWN') House
from table a
union all
SELECT DECODE(1, '1', 'CH', 'UNKOWN') House
from table b......

Can I get CH in the first select , in other words
Can I do this in a simple decode?

I am trying to use the decode func to pull values
from 2 different tables.

Hi Tom,
How null is stored in Oracle??
Either as a zero length character string or any other format??

Let us we have empolyee table and having id,name and salary column. salary have 1000, 200, 2500, 3000, 5000 and so on..
I need to show empolyee grading depending on salary as
sal >= 1000 Grade A
sal >=2800 Grade B
sal >= 4000 Grade C
and sal >=5000 Grade D

How I can use decode in the case.

and e.item_number = decode( '{@parameter_name}', null,
e.item_number,
to_number( '{@parameter_name}' ) )

I was trying to *replace* the following code with the
above (since the above is more readable). I could
also use nvl2 above:

and
(
(
'{@parameter_name}' is not null
and e.item_number = to_number('{@parameter_name}')
)
or
'{@parameter_name}' is null
)

but they dont seem to be equivalent whereas I was
expecting them to be. I think I am missing something
simple - but cant figure it out! If short circuiting
works for decode, then both the above code should
be equivalent, correct?

(parameter_name is a URL parameter, which is a string
but actually needs to be converted into a number.
The syntax of using @parameter_name simply extracts
the string value within the single quotes.

Thanx for your help!

e.item_number = decode( '{@parameter_name}', null,
e.item_number,
to_number( '{@parameter_name}' ) )
---
So the above is to be read as:
if( '{@parameter_name}' is null then,
e.item_number = e.item_number (or a no-op)
else
e.item_number = to_number( '{@parameter_name}' )
end if


And the "equivalent" I gave is

(
(
'{@parameter_name}' is not null
and e.item_number = to_number('{@parameter_name}')
)
or
'{@parameter_name}' is null
)

says '{@parameter_name}' is not null and
e.item_number = to_number({@parameter_name}') /* equivalent to else clause in the previous case */

or
'{@parameter_name}' is null - dont do anything - or
a no-op. /* equivalent to if clause in the previous case*/

So are not they equivalent?

I can see myself smacking my head once I get it!:)

I need to append an additional condition to my 'Where' Clause, depending on if the :2 parameter is passed or not. The entire statement should then be executed. I tried it as follows - which obviously didn't work!!

SELECT * FROM a
WHERE rgn_cd = NVL (:1, rgn_cd)
(CASE WHEN :2 IS NOT NULL THEN
AND name IN (SELECT name
FROM b
WHERE team_cd = NVL(:2,team_cd) )
END )

Thanks, Krish

Sorry still dont see it..
I understand that variation and I also know
the meaning of decode in general.

OK, let me put it to you this:
How would you read in English the following?:

e.item_number = decode( '{@parameter_name}', null,
e.item_number,
to_number( '{@parameter_name}' ) )

and why is it not the same as:

if( '{@parameter_name}' is null then,
e.item_number = e.item_number (or a no-op)
else
e.item_number = to_number( '{@parameter_name}' )
end if

The statement works for cases where '{@parameter_name}' is
not null but does not return anything if parameter
name is null. Why would e.x = e.x result in false in
such cases (when '{@parameter_name}' is null)?

Thanx!

CREATE TABLE test_log (foo NUMBER);

CREATE FUNCTION log_me(p_foo IN NUMBER) RETURN NUMBER
IS
pragma autonomous_transaction;
BEGIN
INSERT INTO test_log VALUES(p_foo);
COMMIT;
RETURN p_foo;
END;
/

-- first argument is not null;
-- no need to evaluate second but is done nonetheless
SELECT NVL(log_me(1), log_me(2)) FROM DUAL;

-- first argument is not null;
-- no need to evaluate any of the following
-- which indeed doesn't happen
SELECT coalesce(log_me(3), log_me(4), log_me(5)) FROM DUAL;

-- first argument is not null; second is returned,
-- no need to evaluate third but is done nonetheless
SELECT NVL2(1, log_me(6), log_me(7)) FROM DUAL;

-- key matches with first option; first value is returned
-- other options or values aren't evaluated
SELECT DECODE
(8,
log_me(8), log_me(9),
log_me(10), log_me(11),
log_me(12)
)
FROM DUAL;

-- Now we check the log...
SELECT * FROM test_log;
-- Gives:
-- 1
-- 2 <- unnecessary evaluation in NVL example
-- 3
-- 6
-- 7 <- unnecessary evaluation in NVL2 example
-- 8
-- 9

-- So Tom, please tell us why? Especially NVL seems to be
-- a nothing but a specialised case of COALESCE, so why is
-- the latter short circuited while the former isn't?
-- Are there any reasons to NOT use COALESCE and DECODE
-- instead of NVL and NVL2?

Another straightforward way to see that DECODE and CASE both "short circut":

SQL> select DECODE(1, null, 1/0, 777) from dual;

DECODE(1,NULL,1/0,777)
----------------------
                   777

SQL> select CASE WHEN 1 = 2 THEN 1/0 ELSE 777 END
  2    from dual;

CASEWHEN1=2THEN1/0ELSE777END
----------------------------
                         777

E.g. '1/0' had never been evaluated and everything went fine.

Now consider this:

SQL> select NVL(1, 1/0) from dual;
select NVL(1, 1/0) from dual
               *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select COALESCE(1, 2, 3, 1/0) from dual;
select COALESCE(1, 2, 3, 1/0) from dual
                          *
ERROR at line 1:
ORA-01476: divisor is equal to zero

Best regards,
Andrew. 

SQL> show release
release 1001000200
SQL> select nvl(1,1/0) from dual;
select nvl(1,1/0) from dual
              *
FOUT in regel 1:
.ORA-01476: divisor is equal to zero


SQL> select coalesce(1,2,3,1/0) from dual;

COALESCE(1,2,3,1/0)
-------------------
                  1

Works fine for me. So perhaps some behaviour HAS changed between versions? I'm using 10g as you can see; Andrew, what version are you using? 

CREATE OR REPLACE FUNCTION VALIDATE_VIN(IN_VIN IN VARCHAR2) RETURN NUMBER AS

--Author: Rajnikant Tahlyan
--Date: 13-Feb-2007


--MEANING OF DIFFERENT CHARACTERS IN A VIN
--CHARACTER 1 --> COUNTRY
--CHARACTER 2 --> MANUFACTURER
--CHARACTER 3 --> MAKE
--CHARACTER 4-6 --> ENGINE
--CHARACTER 7 --> BODY/TRANSMISSION
--CHARACTER 8 --> TRIM LEVEL/RESTRAINT
--CHARACTER 9 --> CHECK DIGIT
--CHARACTER 10 --> MODEL YEAR
--CHARACTER 11 --> ASSEMBLY PLANT
--CHARACTER 12-17 --> SERIAL NUMBER


V_CHECK_DIGIT_NUM NUMBER;
V_CHECK_DIGIT VARCHAR2(1);

--VALUES ASSIGNED TO CHARACTERS AS PER "SUPPLEMENT TO TRUCK BLUE BOOK - 1999 TRUCK IDENTIFICATION"
V_A NUMBER := 1;
V_B NUMBER := 2;
V_C NUMBER := 3;
V_D NUMBER := 4;
V_E NUMBER := 5;
V_F NUMBER := 6;
V_G NUMBER := 7;
V_H NUMBER := 8;
V_J NUMBER := 1;
V_K NUMBER := 2;
V_L NUMBER := 3;
V_M NUMBER := 4;
V_N NUMBER := 5;
V_P NUMBER := 7;
V_R NUMBER := 9;
V_S NUMBER := 2;
V_T NUMBER := 3;
V_U NUMBER := 4;
V_V NUMBER := 5;
V_W NUMBER := 6;
V_X NUMBER := 7;
V_Y NUMBER := 8;
V_Z NUMBER := 9;



BEGIN

IF (LENGTH(IN_VIN) <> 17) THEN
RETURN 1; --FAILURE --> ERRORNEOUS VIN, AFTER 1981 EACH VIN HAS TO BE OF 17 CHARACTERS - EITHER THE VIN IS FAKE OR EARLIER THAN 1981
ELSE
--USE FORMULA TO CALCULATE THE CHECK DIGIT
SELECT MOD((DECODE(SUBSTR(IN_VIN,1,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,1,1))*8 +
DECODE(SUBSTR(IN_VIN,2,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,2,1))*7 +
DECODE(SUBSTR(IN_VIN,3,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,3,1))*6 +
DECODE(SUBSTR(IN_VIN,4,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,4,1))*5 +
DECODE(SUBSTR(IN_VIN,5,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,5,1))*4 +
DECODE(SUBSTR(IN_VIN,6,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,6,1))*3 +
DECODE(SUBSTR(IN_VIN,7,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,7,1))*2 +
DECODE(SUBSTR(IN_VIN,8,1) ,'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,8,1))*10 +
DECODE(SUBSTR(IN_VIN,10,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,10,1))*9 +
DECODE(SUBSTR(IN_VIN,11,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,11,1))*8 +
DECODE(SUBSTR(IN_VIN,12,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,12,1))*7 +
DECODE(SUBSTR(IN_VIN,13,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,13,1))*6 +
DECODE(SUBSTR(IN_VIN,14,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,14,1))*5 +
DECODE(SUBSTR(IN_VIN,15,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,15,1))*4 +
DECODE(SUBSTR(IN_VIN,16,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,16,1))*3 +
DECODE(SUBSTR(IN_VIN,17,1),'A',V_A,'B',V_B,'C',V_C,'D',V_D,'E',V_E,'F',V_F,'G',V_G,'H',V_H,'J',V_J,'K',V_K,'L',V_L,'M',V_M,'N',V_N,'P',V_P,'R',V_R,'S',V_S,'T',V_T,'U',V_U,'V',V_V,'W',V_W,'X',V_X,'Y',V_Y,'Z',V_Z,SUBSTR(IN_VIN,17,1))*2),11)
INTO V_CHECK_DIGIT_NUM
FROM DUAL;

--IF CHECK DIGIT IS 10, USE X ELSE WHAT EVER IS RETURNED I.E. 0-9
IF (V_CHECK_DIGIT_NUM = 10) THEN
V_CHECK_DIGIT := 'X';
ELSE
V_CHECK_DIGIT := TO_CHAR(V_CHECK_DIGIT_NUM);
END IF;

--IF THE CALCULATED CHECK DIGIT MATCHES THE 9TH CHARACTER OF VIN, RETURN SUCCESS [0] ELSE FAILURE [1]
IF (V_CHECK_DIGIT = SUBSTR(IN_VIN,9,1)) THEN
RETURN 0; --SUCCESS, THE VIN IS GENUINE
ELSE
RETURN 1; --FAILURE, CHECK DIGIT MISMATCH, THE VIN IS FAKE
END IF;

END IF;

END VALIDATE_VIN;

Consider:

create sequence MySQ ;
select MySQ.NEXTVAL from dual ;
select coalesce( 123, MySQ.NEXTVAL ) from dual ;
select MySQ.CURRVAL from dual ;

Just being curious I did as follows:

create table vins_x (vin varchar2(17 byte));
create table vins_u (vin varchar2(17 byte));

insert into vins_x
select vin 
  from (select vin
          from (select replace(replace(replace(dbms_random.string('X',17),'I','J'),'O','P'),'Q','R') vin 
                  from dual
                connect by level <= 1000000
               )
       );
insert into vins_u
select vin 
  from (select vin
          from (select replace(replace(replace(dbms_random.string('U',17),'I','J'),'O','P'),'Q','R') vin 
                  from dual
                connect by level <= 1000000
               )
       );

replace are there just to make your function run.

create or replace FUNCTION validate_vin_new(in_vin in varchar2) return number as 
letters     varchar2(26) := 'ABCDEFGHJKLMNPRSTUVWXYZIOQ';
digits      varchar2(23) := '12345678123457923456789';
translation varchar2(23) := translate(in_vin,letters,digits);
weights     varchar2(17) := '8765432A098765432';
width       pls_integer  := 17;
check_sum   pls_integer  := 0;
temp_digit  pls_integer;
check_digit varchar2(1);
begin
 if (length(translation) <> width) then 
    return 1;
  else
    for i in 1 .. width loop
      if i = 8 then
        temp_digit := 10;
      else
        temp_digit := to_number(substr(weights,i,1));
      end if;
      check_sum := check_sum + temp_digit * to_number(substr(translation,i,1));
    end loop;
    temp_digit := mod(check_sum,11);
    if temp_digit = 10 then
      check_digit := 'X';
    else
      check_digit := to_char(temp_digit);
    end if;
    if check_digit = substr(in_vin,9,1) then
      return 0;
    else
      return 1;
    end if;
 end if;
end validate_vin_new;

then I run the following procedures (validate_vin_old is of course your procedure copy/pasted)

select sum(case when test = 0 then 1 end) positive,sum(case when test = 1 then 1 end) negative 
  from (select vin,validate_vin_old(vin) test
          from vins_u
       );
select sum(case when test = 0 then 1 end) positive,sum(case when test = 1 then 1 end) negative 
  from (select vin,validate_vin_new(vin) test
          from vins_u
       );
select sum(case when test = 0 then 1 end) positive,sum(case when test = 1 then 1 end) negative 
  from (select vin,validate_vin_old(vin) test
          from vins_x
       );
select sum(case when test = 0 then 1 end) positive,sum(case when test = 1 then 1 end) negative 
  from (select vin,validate_vin_new(vin) test
          from vins_x
       );

the outer selects are there just to force completion.
And these are the results:

TABLE validate_old validate_new
====================================
vins_u       214.482          64.667
vins_u       215.185          60.205
vins_u       211.716          59.553

vins_x       222.029          60.437
vins_x       226.090          60.524
vins_x       220.201          62.809

on average

TABLE validate_old validate_new
====================================
vins_u       213.794          61.475
vins_x       222.773          61.257

The results may be of some interest to you and/or others.

Regards

Etbin

Tom,

First thank you in advanced. Is there a better way, to write this query.


SELECT wbr.global_student, 'Student_PROCESSED', 'SCH', SYSDATE, NULL, NULL
FROM APP.REGION_OWNER AAA
WHERE NVL (status, '0') IN ('0', '9', '8')
AND NVL (subsidiary, '0') IN ('0', '9', '8')
AND APP.cross_ref_effective_dt =
(SELECT max( app.cross_ref_effective_dt)
FROM APP.REGION_OWNER wbr
WHERE AAA.duns_nr = AA1.duns_nr )