6 Replies Latest reply: Nov 19, 2012 5:06 AM by odie_63 RSS

    MODEL applying Rules in ORDER

    887479
      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
          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
            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
              Or is it not possible?
              • 4. Re: MODEL applying Rules in ORDER
                Manik
                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
                  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
                    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