1 2 Previous Next 15 Replies Latest reply: Sep 26, 2013 3:21 AM by Hoek RSS

    model clause example

    a_cute_person
      I want to learn about model clause, if anybody has learnt pls share your knowledge, it will be helpful to everybody, the examples provided in oracle documentation are really pathetic. pls share your knowledge with good examples which any person can understand, like

      what is a measure and what it does ?
      what is a dimension clause ?
      what is rules how to use and what it does.

      if oracle documentation is reading my post hope they will understand how difficult it is to understand the examples provided in oracle documentation
        • 1. Re: model clause example
          asahide
          Hello,

          Hope this helps..
          <<http://www.oreillynet.com/pub/a/network/2004/08/10/MODELclause.html>>
          <<http://www.sqlsnippets.com/en/topic-11663.html>>

          Rgd,
          • 2. Re: model clause example
            jeneesh
            If you are familiar with OOPS concepts, I think it will be very easy to understand the MODEL concept..Let me try to explain with a simple example.

            Think of an array, for example POPULATION_ARRAY, which will store the population for countries for different years.Means the array is defined as

            POPULATION_ARRAY(population number) indexed by [COUNTRY_ID,YEAR]

            We normally define the values like below for arrays..
             
                population_array[INDIA,2012].population := 130,000,000;
            Here the ID of the country and YEAR are the dimensions. POPULATION is a MEASURE.

            Another element in the array will be
                population_array[CHINA,2012].population := 150,000,000;
            Now suppose I want to do a calculation to find out the population on 2022 on an assumtion that the population will be double(!!!) for these countries in 2022.
            You will be knowing how can you do that in EXCEL.Same thing can be done using MODEL. (And much more..!)

            For simplicity we will populate all these data in a single table.
            SQL> create table country(country_id varchar2(30),
              2  year number,population number);
            
            Table created.
            
            SQL> insert into country values('INDIA',2012,130000000);
            
            1 row created.
            
            SQL> insert into country values('CHINA',2012,150000000);
            
            1 row created.
            
            SQL> commit;
            
            Commit complete.
            
            SQL> select * from country;
            
            COUNTRY_ID                           YEAR POPULATION
            ------------------------------ ---------- ----------
            INDIA                                2012  130000000
            CHINA                                2012  150000000
            Here we dont have values for 2022.Now how will we do the calculation and display the results using MODEL?

            As mentioned, the dimensions are COUNTRY_ID,YEAR. The fatc we are interested is the POPULATION.

            So, the MODEL query will be as follows, for INDIA
            SQL> select *
              2  from country
              3  model
              4   dimension by (country_id,year)
              5   measures(population)
              6    rules upsert all
              7     (
              8      population['INDIA',2022] = population['INDIA',2012]*2
              9     );
            
            COUNTRY_ID                           YEAR POPULATION
            ------------------------------ ---------- ----------
            CHINA                                2012  150000000
            INDIA                                2012  130000000
            INDIA                                2022  260000000
            So, here we are using MODEL just like an EXCEL sheet.

            You can notice that we hard coded the country name here,That can be avoided by using ANY key word and cv (current value) function.
            SQL> select *
              2  from country
              3  model
              4   dimension by (country_id,year)
              5   measures(population)
              6    rules upsert all
              7     (
              8      population[ANY,2022] = population[cv(),2012]*2
              9     );
            
            COUNTRY_ID                           YEAR POPULATION
            ------------------------------ ---------- ----------
            INDIA                                2012  130000000
            CHINA                                2012  150000000
            CHINA                                2022  300000000
            INDIA                                2022  260000000
            Now suppose, I dont have the details of USA for 2012. But I assume the population of USA is half of that of INDIA.That can be added as follows
            SQL> select *
              2  from country
              3  model
              4   dimension by (country_id,year)
              5   measures(population)
              6    rules upsert all
              7     (
              8      population['USA',ANY] = population['INDIA',cv()]/2,
              9      population[ANY,2022] = population[cv(),2012]*2
             10     );
            
            COUNTRY_ID                           YEAR POPULATION
            ------------------------------ ---------- ----------
            INDIA                                2012  130000000
            CHINA                                2012  150000000
            USA                                  2012   65000000
            CHINA                                2022  300000000
            INDIA                                2022  260000000
            USA                                  2022  130000000
            
            6 rows selected.
            With this basics, go throuth http://docs.oracle.com/cd/B28359_01/server.111/b28313/sqlmodel.htm, and ask for specific doubts..

            Edited by: jeneesh on Jan 30, 2013 1:40 PM
            Please note: This is a very simple sample, which may not require the MODEL clause as such..
            • 3. Re: model clause example
              a_cute_person
              beautiful !!! THOMAS KYTE of asktom.oracle.com and documentation team of oracle must learn how to give examples for new features
              • 4. Re: model clause example
                chris227
                Think of it as an excel sheet:
                970426 wrote:
                what is a measure and what it does ?
                A measure is the equivalent to a column in an excel sheet. Could be a column of the underlying table/view or a newly defined one.
                what is a dimension clause ?
                The dimension is like the rownumber in excel. It must be (in most cases) unique to identifiy a row of the sheet (model) unambiguously (within a partiton when used).
                Could be artifical (e.g. dimension by (row_number() over (partition by .. order by ..) rn or build by one or more columns of the underlying table.
                It is like a primary key of your model for rows.
                Together with a measure it could adresse one or more or all cells of a measure column.
                what is rules how to use and what it does.
                The rules are like excel formulas applied to alter existing values or generate new cells.

                This one is not so bad

                https://docs.google.com/presentation/d/1zwMNPb4eRyd_uccNW-PitkX3_hOIzccNs_qkwOCuFhA/present?pli=1&ueb=true#slide=id.i0

                regards

                Edited by: chris227 on 30.01.2013 00:55
                • 5. Re: model clause example
                  a_cute_person
                  using ur wonderful example i tried to calculate dept wise salary for all employees and wrote this query, but its not working

                  select ename,sal
                  from emp
                  model
                  dimension by (deptno,ename)
                  measures (sal)
                  rules(
                  sal[deptno,ename] = sal [ sum(sal),0] );
                  • 6. Re: model clause example
                    a_cute_person
                    using ur wonderful example i tried to calculate dept wise salary for all employees and wrote this query, but its not working

                    select ename,sal
                    from emp
                    model
                    dimension by (deptno,ename)
                    measures (sal)
                    rules(
                    sal[deptno,ename] = sal [ sum(sal),0] )
                    • 7. Re: model clause example
                      a_cute_person
                      thanks chris but the example in the link https://docs.google.com/presentation/d/1zwMNPb4eRyd_uccNW-PitkX3_hOIzccNs_qkwOCuFhA/present?pli=1&ueb=true#slide=id.i0 is "complicated" i need something which is more simple to understand
                      • 8. Re: model clause example
                        jeneesh
                        You are using it for a wrong purpose..

                        MODEL is not used for GROUP claculations, it can be used for constructing some kind of LOOPING in SQL (Without using PL/SQL)..

                        What you are trying to do can be done with GROUP BY
                        select deptno,ename,sum(sal) sal
                        from emp
                        group by deptno,ename;
                        You should not use a hammer with a screw..You will be able to do it in a complicated way, but - Screw driver is the right tool for that..
                        • 9. Re: model clause example
                          chris227
                          select
                          *
                          from emp
                          model
                          RETURN UPDATED ROWS
                          partition by (deptno)
                          dimension by (empno)
                          measures (sal)
                          rules(
                          sal[0] = sum(sal)[empno]
                          )
                          • 10. Re: model clause example
                            chris227
                            Model is not an easy one to learn.
                            Dont let cow down form the first pages. Look at pages 16/17 for example. Just the same as given by jeneesh.
                            • 11. Re: model clause example
                              chris227
                              jeneesh wrote:
                              You are using it for a wrong purpose..

                              MODEL is not used for GROUP claculations, it is used for constructing some kind of LOOPING in SQL (Without using PL/SQL)..
                              It mainly for the purpose of calculating new cells/values based on a table/view.
                              You may loop (for-loop, iteration-rules) but you dont have to.
                              • 12. Re: model clause example
                                a_cute_person
                                chris

                                this was ur example

                                select
                                nvl(to_char(empno),'Total for deptno '||deptno)
                                empno
                                *,sal*
                                from emp
                                model
                                partition by (deptno)
                                dimension by (row_number() over(
                                partition by deptno
                                order by empno) rn)
                                measures (sal, empno)
                                rules(
                                *sal[0]=sum(sal) [any]*
                                *)*
                                order by
                                deptno, empno

                                i removed the deptno, can u pls explain what is wrong with

                                select ename,sal
                                from emp
                                model
                                partition by (deptno)
                                dimension by (deptno,ename)
                                measures (sal)
                                rules(
                                sal[any,any] = sal [ sum(sal),0] );

                                in my example i did partition by deptno and why am i getting group function not allowed error ?
                                • 13. Re: model clause example
                                  a_cute_person
                                  should i mark the first response of jeneesh as correct , nobody else is aware of model clause ??
                                  • 14. Re: model clause example
                                    chris227
                                    970426 wrote:
                                    i removed the deptno, can u pls explain what is wrong with
                                    partition by (deptno)
                                    dimension by (deptno,ename)
                                    sal[any,any] = sal [ sum(sal),0] );

                                    in my example i did partition by deptno and why am i getting group function not allowed error ?
                                    sal [ sum(sal),0] this is a cell-reference.
                                    You cant use a group function inside a cell reference.
                                    
                                    Now you might try
                                    
                                    sal[any,any] = sum(sal)[cv(),any]);
                                    ORA-32637: Self cyclic rule in sequential order MODEL
                                    
                                    That is because you try to assign the sum to a cell used in the sum-calculation itself.
                                    
                                    So you can try the way i showed you above or add aother measure:
                                    
                                    measures (sal, 0 sum_sal)
                                    rules(
                                    sum_sal[any,any] = sum(sal)[cv(),any]);
                                    
                                    In addition
                                    
                                    partition by (deptno)
                                    dimension by (deptno,ename)
                                    
                                    thats the same thing twice semantically.
                                    You just need deptno as partition or dimension to be unambigously.
                                    The usage as partition will be more efficient and simpler, because the calculation
                                    for each partition is done separatly and you have to use one dimension less
                                    in the cell adressing
                                    Edited by: chris227 on 30.01.2013 05:02
                                    1 2 Previous Next