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

MODEL applying Rules in ORDER

887479 Newbie
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
    Manik Expert
    Currently Being Moderated
    I think you should try that requirement this way:
    Please check ...

    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
    887479 Newbie
    Currently Being Moderated
    Manik wrote:
    I think you should try that requirement this way:
    Please check ...

    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
    887479 Newbie
    Currently Being Moderated
    Or is it not possible?
  • 4. Re: MODEL applying Rules in ORDER
    Manik Expert
    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
    887479 Newbie
    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
    odie_63 Guru
    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
  • Helpful Answers - 5 points