Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent:

select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/sum(v)[any] );  --plain sum
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5 
         2        100        0.5 
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/nullif(sum(v)[any],0) ); --with nullif
/*
         K          V        SHR
---------- ---------- ----------
         1        100       0.25   <------\___ why?
         2        100       0.25   <------/
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/decode(sum(v)[any],0,null,sum(v)[any]) );  -- with decode
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5 
         2        100        0.5 
*/

What am I missing about the processing of rules that explains this behaviour?

SQLFiddle here

share|improve this question
1  
Bonkers. Will investigate! –  FreshPhilOfSO Aug 18 '13 at 0:17
    
Have you reported the bug or looked on Metalink? –  FreshPhilOfSO Aug 22 '13 at 23:45
    
No, I wasn't sure I wasn't missing something obvious at first. Have you got 12c to test? –  Jack Douglas Aug 23 '13 at 6:56
    
same behaviour in 12c –  miracle173 Sep 4 '13 at 10:23
add comment

1 Answer

In the Oracle Database SQL Language Reference one can read

NULLIF(expr1, expr2) ... The NULLIF function is logically equivalent to the following CASE expression: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

but Oracle 11.2.0.3 on Linux gives the following


SQL>select *
  2  from (select level k, 100 v from dual connect by level<=2)
  3  model return updated rows
  4  dimension by (k)
  5  measures (v, 0 shr)
  6  rules ( shr[any] = CASE WHEN sum(v)[any] = 0 THEN NULL ELSE sum(v)[any] END);


         K          V        SHR
---------- ---------- ----------
         1        100        200
         2        100        200

SQL> select *
  2  from (select level k, 100 v from dual connect by level<=2)
  3  model return updated rows
  4  dimension by (k)
  5  measures (v, 0 shr)
  6  rules ( shr[any] = nullif(sum(v)[any], 0));

         K          V        SHR
---------- ---------- ----------
         1        100        400
         2        100        400

SQL>

Therefor this is (at least a documentation :-) bug in 11r2

One gets the same results in Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.