This discussion is archived
6 Replies Latest reply: Nov 19, 2012 3:06 AM by odie_63

MODEL applying Rules in ORDER

Currently Being Moderated
Hi,

11gR2 DB.

Please see the below output...

My understandig is taht -The rule " c2[c1>1]=c2[cv()-1]+c3[cv()-1]" is getting applied together for all C1>1, and then the rule "c3[c1>1] = c2[cv()-1]" is getting applid for all C1>1.

How can apply the rules sequentially? ie -- find c2[2], then c3[2], then c2[3] then c3[3]....
``````  with t as
( select 1 c1,10 c2 from dual union all
select 2 c1,20 c2 from dual union all
select 3 c1,30 c2 from dual union all
select 4 c1,40 c2 from dual-- union all
)
select *
from t
model
dimension by (c1)
measures (c2,0 as c3)
rules upsert sequential order
(
c2[c1>1]=c2[cv()-1]+c3[cv()-1],
c3[c1>1] = c2[cv()-1]
);

C1 C2 C3
-- -- --
1 10  0
2 10 10
3 10 10
4 10 10 ``````
thanks
• 1. Re: MODEL applying Rules in ORDER
Currently Being Moderated
I think you should try that requirement this way:

``````WITH t AS
(SELECT 1 c1, 10 c2 FROM DUAL
UNION ALL
SELECT 2 c1, 20 c2 FROM DUAL
UNION ALL
SELECT 3 c1, 30 c2 FROM DUAL
UNION ALL
SELECT 4 c1, 40 c2 FROM DUAL                             -- union all
)
SELECT *
FROM t
MODEL
DIMENSION BY (c1)
MEASURES (c2, 0 AS c3)
RULES
ITERATE (4)
(c2 [c1 > 1] = c2[CV () - 1] + c3[CV () - 1],
c3 [c1 > 1] = c2[CV () - 1]);``````
output:
``````C1     C2     C3
1     10     0
2     10     10
3     20     10
4     30     20``````
Other way to write it:
``````WITH t AS
(SELECT 1 c1, 10 c2 FROM DUAL
UNION ALL
SELECT 2 c1, 20 c2 FROM DUAL
UNION ALL
SELECT 3 c1, 30 c2 FROM DUAL
UNION ALL
SELECT 4 c1, 40 c2 FROM DUAL),
t1 AS
(SELECT c1, LAG (c2, 1, c2) OVER (PARTITION BY 1 ORDER BY 1) c2
FROM t)
SELECT c1, c2, LAG (c2, 1, 0) OVER (PARTITION BY 1 ORDER BY 1) c3
FROM t1;``````
Edited: Included another method for obtaining the result (instead of MODEL)

Cheers,
Manik.
• 2. Re: MODEL applying Rules in ORDER
Currently Being Moderated
Manik wrote:
I think you should try that requirement this way:

``````WITH t AS
(SELECT 1 c1, 10 c2 FROM DUAL
UNION ALL
SELECT 2 c1, 20 c2 FROM DUAL
UNION ALL
SELECT 3 c1, 30 c2 FROM DUAL
UNION ALL
SELECT 4 c1, 40 c2 FROM DUAL                             -- union all
)
SELECT *
FROM t
MODEL
DIMENSION BY (c1)
MEASURES (c2, 0 AS c3)
RULES
ITERATE (4)
(c2 [c1 > 1] = c2[CV () - 1] + c3[CV () - 1],
c3 [c1 > 1] = c2[CV () - 1]);``````
output:
``````C1     C2     C3
1     10     0
2     10     10
3     20     10
4     30     20``````
Other way to write it:
``````WITH t AS
(SELECT 1 c1, 10 c2 FROM DUAL
UNION ALL
SELECT 2 c1, 20 c2 FROM DUAL
UNION ALL
SELECT 3 c1, 30 c2 FROM DUAL
UNION ALL
SELECT 4 c1, 40 c2 FROM DUAL),
t1 AS
(SELECT c1, LAG (c2, 1, c2) OVER (PARTITION BY 1 ORDER BY 1) c2
FROM t)
SELECT c1, c2, LAG (c2, 1, 0) OVER (PARTITION BY 1 ORDER BY 1) c3
FROM t1;``````
Edited: Included another method for obtaining the result (instead of MODEL)

Cheers,
Manik.
Thanks for the response..

Actually I am not trying to get that particular output. I was asking what is the way in MODEL, to apply rules in the order I have mentioned in the first post...

Your query is not doing that, for example, the below query also will give the same output..
``````WITH t AS
(SELECT 1 c1, 10 c2 FROM DUAL
UNION ALL
SELECT 2 c1, 20 c2 FROM DUAL
UNION ALL
SELECT 3 c1, 30 c2 FROM DUAL
UNION ALL
SELECT 4 c1, 40 c2 FROM DUAL                             -- union all
)
SELECT *
FROM t
MODEL
DIMENSION BY (c1)
MEASURES (c2, 0 AS c3)
RULES
ITERATE (2)--"Changed to 2. If you changed to 3 or 5 also you will get the same output."
(c2 [c1 > 1] = c2[CV () - 1] + c3[CV () - 1],
c3 [c1 > 1] = c2[CV () - 1]);``````
• 3. Re: MODEL applying Rules in ORDER
Currently Being Moderated
Or is it not possible?
• 4. Re: MODEL applying Rules in ORDER
Currently Being Moderated
Check this if it helps:

An ordered rule is one that has ORDER BY specified on the left side. It accesses cells in the order prescribed by ORDER BY and applies the right side computation.

http://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmodel.htm

c2[c1>1] order by c1=.......

What is your expected output there...?

Cheers,
Manik.
• 5. Re: MODEL applying Rules in ORDER
Currently Being Moderated
That data is just explain what I wanted to ask..

My question is - how can we apply the rules in that order what I explained in the first post..
• 6. Re: MODEL applying Rules in ORDER
Currently Being Moderated
``````SQL> with t as
2  ( select 1 c1,10 c2 from dual union all
3    select 2 c1,20 c2 from dual union all
4    select 3 c1,30 c2 from dual union all
5    select 4 c1,40 c2 from dual-- union all
6  )
7  select *
8  from t
9  model
10   dimension by (c1)
11   measures (c2,0 as c3)
12   rules automatic order
13   (
14    c2[c1 > 1] = c2[cv()-1]+c3[cv()-1],
15    c3[c1 > 1] = c2[cv()-1]
16    );

C1         C2         C3
---------- ---------- ----------
1         10          0
2         10         10
3         20         10
4         30         20
``````

Legend

• Correct Answers - 10 points