Using MODEL clause in Oracle and returning previous values

user_17306445 0
Viewed 117

I have a query that should return V * previous C when MOD (rn, 2)! = 0, and should return V / previous C when MOD (rn, 2) = 0 (for each row sorted by rn).

select *  
FROM (
       Select 1 rn, 1 V from dual  UNION ALL
       Select 2 rn, 2 V from dual  UNION ALL
       Select 3 rn, 4 V from dual  UNION ALL
       Select 4 rn, 2 V from dual  UNION ALL
       Select 5 rn, 3 V from dual  

)
MODEL
  DIMENSION BY (rn)
  MEASURES ( V, 0 c)
  RULES (
   c[MOD(rn, 2) != 0] = nvl(c[cv()-1],1)*V[cv()]
  ,c[MOD(rn, 2) = 0] = c[cv()-1] /V[cv()]
  -- c[any] = nvl(c[cv()-1],1) *B_VOL[cv()]
  ) order by 1;

This is example of valid output:

ROWNUM V C
1 1 1
2 2 0.5
3 4 2
4 2 1
5 3 3

But my query return this (incorrect output):

ROWNUM V C
1 1 1
2 2 0.5
3 4 0
4 2 0
5 3 0

Please, help me fix my query.

oracle sql
1 Answers

You can put the MODulo filter in a CASE expression on the right-hand side of the assignment rather than trying to have odd- and even-row rules:

select *  
FROM (
       SELECT 1 rn, 1 V from dual  UNION ALL
       Select 2 rn, 2 V from dual  UNION ALL
       Select 3 rn, 4 V from dual  UNION ALL
       Select 4 rn, 2 V from dual  UNION ALL
       Select 5 rn, 3 V from dual  
)
MODEL
  DIMENSION BY (rn)
  MEASURES ( V, 0 AS c)
  RULES (
    c[1] = V[1],
    c[rn>1] = CASE MOD(cv(rn), 2)
              WHEN 0
              THEN c[cv()-1] / V[cv()]
              ELSE c[cv()-1] * V[cv()]
              END
)
order by rn;

Which outputs:

RN V C
1 1 1
2 2 .5
3 4 2
4 2 1
5 3 3

db<>fiddle here

Update

Your query works if you use AUTOMATIC ORDER (rather than the default SEQUENTIAL ORDER):

select *  
FROM (
       Select 1 rn, 1 V from dual  UNION ALL
       Select 2 rn, 2 V from dual  UNION ALL
       Select 3 rn, 4 V from dual  UNION ALL
       Select 4 rn, 2 V from dual  UNION ALL
       Select 5 rn, 3 V from dual  

)
MODEL
  DIMENSION BY (rn)
  MEASURES ( V, 0 c)
  RULES AUTOMATIC ORDER (
   c[MOD(rn, 2) != 0] = nvl(c[cv(rn)-1],1)*V[cv(rn)]
  ,c[MOD(rn, 2) = 0] = c[cv(rn)-1] /V[cv(rn)]
  )
order by 1;

db<>fiddle here

user_15092640
Related