Home » SQL & PL/SQL » SQL & PL/SQL » SQL Model Clause , Please Help (Oracle 10g Rel.2 )
SQL Model Clause , Please Help [message #578359] Wed, 27 February 2013 04:38 Go to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
I want to use Model Clause to add a new column "TF" which contain TRUE or FALSE on the basis of

* if in Current row Deptno and Job are same as Previous row Deptno and Job then TRUE else FALSE

Ignore to have TRUE or FALSE on very first record.

Please help how to achieve.

scott@ Test.DB> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.



scott@ Test.DB> select * from emp order by 3;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO TF
--------- ---------- --------- --------- --------- --------- --------- --------- ------
     7788 SCOTT      ANALYST        7566 19-APR-87      3000         0        20  FALSE
     7902 FORD       ANALYST        7566 03-DEC-81      3000         0        20  TRUE
     7934 MILLER     CLERK          7782 23-JAN-82      1300         0        10  FALSE
     7900 JAMES      CLERK          7698 03-DEC-81       950         0        30  FALSE
     7369 SMITH      CLERK          7902 17-DEC-80       800         0        20  FALSE
     7876 ADAMS      CLERK          7788 23-MAY-87      1100         0        20  TRUE
     7698 BLAKE      MANAGER        7839 01-MAY-81      2850         0        30  FALSE
     7566 JONES      MANAGER        7839 02-APR-81      2975         0        20  FALSE
     7782 CLARK      MANAGER        7839 09-JUN-81      2450         0        10  FALSE
     7839 KING       PRESIDENT           17-NOV-81      5000         0        10  FALSE
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30  FALSE
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30  TRUE
     7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30  TRUE
     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30  TRUE

14 rows selected.



With best regards.

[Updated on: Wed, 27 February 2013 04:45]

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578361 is a reply to message #578359] Wed, 27 February 2013 04:46 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
is there any specific reason to use only MODEL clause? Have you thought for any alternative?

regards,
Delna

[Updated on: Wed, 27 February 2013 04:47]

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578363 is a reply to message #578361] Wed, 27 February 2013 04:53 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
thanks very much for quick response.

as i have an assignment to produce data which check thre Previous Row to make current row's column specifically, and i have no idea except using Model clause to compare current record with previous record, but from yesterday i am trying to explore it but still failed to have this specific solution even i tried to Search here.

Please help if you can.

Regards.

[Updated on: Wed, 27 February 2013 08:46] by Moderator

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578365 is a reply to message #578363] Wed, 27 February 2013 05:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Google for LAG/LEAD analytical function in Oracle.

regards,
Delna
Re: SQL Model Clause , Please Help [message #578367 is a reply to message #578365] Wed, 27 February 2013 05:30 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
delna.sexy wrote on Wed, 27 February 2013 16:30
Google for LAG/LEAD analytical function in Oracle.

regards,
Delna


thanks for advise.
how i can compare Current Row Columns with Previous Row Columns using Model Clause?

Regards.
Re: SQL Model Clause , Please Help [message #578368 is a reply to message #578367] Wed, 27 February 2013 05:33 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
You said you didnt know how to do it except the model clause.

Lag/lead are better for your requirements - unless it is homework specifically requesting the use of the model clause.

So I suppose the question is: Is there a requirement to use the model clause and if so why? For the described requirement it is like taking a hovercraft to the local shops Wink

[Updated on: Wed, 27 February 2013 05:40]

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578375 is a reply to message #578368] Wed, 27 February 2013 06:03 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member

thanks for your reply.
OK, is there any other solution instead Model Clause, i will be happy as i want to complete the assignment at work ( not Homework ).

how i can compare Current Row Columns with Previous Row Columns in SQL?

previously i have used Running Total in SQL ( getting help from Michel Cadot & Barbara ) which also using previous row column. can i use it to do so or anything else.

Regards.

[Updated on: Wed, 27 February 2013 08:46] by Moderator

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578377 is a reply to message #578375] Wed, 27 February 2013 06:10 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
Have a read on LAG function.
Re: SQL Model Clause , Please Help [message #578382 is a reply to message #578377] Wed, 27 February 2013 06:38 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
Roachcoach wrote on Wed, 27 February 2013 17:40
Have a read on LAG function.


Please check, am i right or any better way to achieve it.
  1  select empno, ename, job, sal, deptno,
  2         case when (job = prvs_job AND deptno = prvs_dpt) then
  3              'TRUE'
  4              else
  5              'FALSE'
  6         end TF
  7    from (
  8          select empno, ename, job, sal, deptno,
  9                 LAG (job,1,0) over(order by job) prvs_job,
 10                 LAG (deptno,1,0) over(order by job) prvs_dpt
 11            from emp
 12           order by job
 13         )
 14* order by job
scott@ Test.DB> /

    EMPNO ENAME      JOB             SAL    DEPTNO TF
--------- ---------- --------- --------- --------- -----
     7788 SCOTT      ANALYST        3000        20 FALSE
     7902 FORD       ANALYST        3000        20 TRUE
     7934 MILLER     CLERK          1300        10 FALSE
     7900 JAMES      CLERK           950        30 FALSE
     7369 SMITH      CLERK           800        20 FALSE
     7876 ADAMS      CLERK          1100        20 TRUE
     7698 BLAKE      MANAGER        2850        30 FALSE
     7566 JONES      MANAGER        2975        20 FALSE
     7782 CLARK      MANAGER        2450        10 FALSE
     7839 KING       PRESIDENT      5000        10 FALSE
     7844 TURNER     SALESMAN       1500        30 FALSE
     7654 MARTIN     SALESMAN       1250        30 TRUE
     7521 WARD       SALESMAN       1250        30 TRUE
     7499 ALLEN      SALESMAN       1600        30 TRUE

14 rows selected.


Regards.
Re: SQL Model Clause , Please Help [message #578383 is a reply to message #578382] Wed, 27 February 2013 06:58 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
For completness here a solution with MODEL-clause Cool
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, tf
  FROM
   (SELECT row_number() over (order by job) rn, 
           e.*, 
           '_____' tf
      FROM emp e)
MODEL
  DIMENSION BY (rn)
  MEASURES (empno, ename, job, mgr, hiredate, sal, comm, deptno, tf)
  RULES
    (tf [ANY] =  
      CASE 
        WHEN    job[CV ()] =    job[CV ()-1]
         AND deptno[CV ()] = deptno[CV ()-1]
          THEN 'TRUE'
          ELSE 'FALSE'
      END);

 EMPNO ENAME      JOB           MGR     HIREDAT        SAL        DEPTNO       TF
-------------------------------------------------------------------------------------

7788	SCOTT	ANALYST	        7566	09.12.1982	3000		20	FALSE
7902	FORD	ANALYST	        7566	03.12.1981	3000		20	TRUE
7934	MILLER	CLERK	        7782	23.01.1982	1300		10	FALSE
7900	JAMES	CLERK	        7698	03.12.1981	950		30	FALSE
7369	SMITH	CLERK	        7902	17.12.1980	800		20	FALSE
7876	ADAMS	CLERK	        7788	12.01.1983	1100		20	TRUE
7698	BLAKE	MANAGER	        7839	01.05.1981	2850		30	FALSE
7566	JONES	MANAGER	        7839	02.04.1981	2975		20	FALSE
7782	CLARK	MANAGER	        7839	09.06.1981	2450		10	FALSE
7839	KING	PRESIDENT		17.11.1981	5000		10	FALSE
7844	TURNER	SALESMAN	7698	08.09.1981	1500	0	30	FALSE
7654	MARTIN	SALESMAN	7698	28.09.1981	1250	1400	30	TRUE
7521	WARD	SALESMAN	7698	22.02.1981	1250	500	30	TRUE
7499	ALLEN	SALESMAN	7698	20.02.1981	1600	300	30	TRUE

[Updated on: Wed, 27 February 2013 07:00]

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578386 is a reply to message #578383] Wed, 27 February 2013 07:10 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member

Thanks very much jum for the specific solution.
This is what i was asking for (how i can compare Current Row Columns with Previous Row Columns using Model Clause?
).
This will be faster than using LAG as i used in previous post? am i correct?

Warmest Regards Smile

Also thanks to Roachcoach and delna.sexy for their interest to guide me.

.

[Updated on: Wed, 27 February 2013 08:46] by Moderator

Report message to a moderator

Re: SQL Model Clause , Please Help [message #578390 is a reply to message #578386] Wed, 27 February 2013 08:05 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
I doubt that the model clause will be faster, I personally would use LAG in this case.
Re: SQL Model Clause , Please Help [message #578391 is a reply to message #578386] Wed, 27 February 2013 08:06 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
[quote title=oralover2006 wrote on Wed, 27 February 2013 08:10]_jum wrote on Wed, 27 February 2013 18:28

Thanks very much jum for the specific solution.
This is what i was asking for (how i can compare Current Row Columns with Previous Row Columns using Model Clause?
).
This will be faster than using LAG as i used in previous post? am i correct?


Why do you think it is faster? I personally would use LAG as it is easier to read, at least for me. An explain plan show the LAG code to be better too.

  1    select empno, ename, job, sal, deptno,
  2             case when (job = prvs_job AND deptno = prvs_dpt) then
  3                  'TRUE'
  4                  else
  5                  'FALSE'
  6             end TF
  7        from (
  8              select empno, ename, job, sal, deptno,
  9                     LAG (job,1,0) over(order by job) prvs_job,
 10                    LAG (deptno,1,0) over(order by job) prvs_dpt
 11               from emp
 12              order by job
 13            )
 14*  order by job
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3116413243

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   994 |     3   (0)| 00:00:01 |
|   1 |  VIEW               |      |    14 |   994 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   350 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   350 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

SQL>
SQL> SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, tf
  2    FROM
  3     (SELECT row_number() over (order by job) rn,
  4             e.*,
  5             '_____' tf
  6        FROM emp e)
  7  MODEL
  8    DIMENSION BY (rn)
  9    MEASURES (empno, ename, job, mgr, hiredate, sal, comm, deptno, tf)
 10    RULES
 11      (tf [ANY] =
 12        CASE
 13      WHEN    job[CV ()] =    job[CV ()-1]
 14           AND deptno[CV ()] = deptno[CV ()-1]
 15            THEN 'TRUE'
 16            ELSE 'FALSE'
 17        END);

Execution Plan
----------------------------------------------------------
Plan hash value: 2247183712

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |  1498 |     4  (25)| 00:00:01 |
|   1 |  SQL MODEL ORDERED   |      |    14 |  1498 |            |          |
|   2 |   VIEW               |      |    14 |  1498 |     4  (25)| 00:00:01 |
|   3 |    WINDOW SORT       |      |    14 |   518 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Re: SQL Model Clause , Please Help [message #578431 is a reply to message #578391] Thu, 28 February 2013 00:33 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
joy_division wrote on Wed, 27 February 2013 19:36

Why do you think it is faster? I personally would use LAG as it is easier to read, at least for me. An explain plan show the LAG code to be better too.



As i read some material from oracle's website ( also downloaded a PDF "SQL MODEL Clause of Oracle Database 10g" An Oracle White Paper August 2003 ) in which they mentioned:
Quote:

An extension to SQL's Select statement, the Model clause enables developers to
treat relational data as multidimensional arrays and define formulas on the
arrays. It offers a concise, easy to read syntax and the functionality needed to
handle demanding calculations. The Model clause resolves formula
dependencies automatically, supporting large sets of interlinked formulas in
sophisticated applications. In addition, Model clause processing employs
advanced optimization techniques and data structures, for very high
performance
. The expressive power and ease of use of the Model clause,
combined with the scalability and manageability of Oracle, provide a major
advance for database applications.


now you have posted Execution Plan and it is a clear picture.
Thanks for your help and advise, now i can use LAG to avoid any Performance related issue, in this case.

Thanks again to all Respected Members of OraFAQ Forum Smile

.

Re: SQL Model Clause , Please Help [message #578479 is a reply to message #578391] Thu, 28 February 2013 10:33 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
joy_division wrote on Wed, 27 February 2013 09:06
Why do you think it is faster? I personally would use LAG as it is easier to read, at least for me. An explain plan show the LAG code to be better too.


LAG is obviously better, but your MODEL isn't optimal either:

SELECT  empno,
        ename,
        job,
        mgr,
        hiredate,
        sal,
        comm,
        deptno,
        tf
  FROM  emp
  MODEL
    PARTITION BY(
                 deptno,
                 job
                )
    DIMENSION BY(
                 row_number() over (partition by deptno,job order by empno) rn
                )
    MEASURES(empno,ename,mgr,hiredate,sal,comm,cast('TRUE' as varchar2(5))tf)
    RULES
      (
       tf[1] = 'FALSE'
      )
  ORDER BY job,
           deptno,
           empno
/ 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO TF
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 FALSE
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 TRUE
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 FALSE
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 FALSE
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 TRUE
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 FALSE
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 FALSE
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 FALSE
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 FALSE
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 FALSE
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 FALSE

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO TF
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 TRUE
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 TRUE
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 TRUE

14 rows selected.


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1980554485

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    14 |   546 |     5  (40)| 00:00:01 |
|   1 |  SORT ORDER BY          |      |    14 |   546 |     5  (40)| 00:00:01 |
|   2 |   SQL MODEL ORDERED FAST|      |    14 |   546 |     5  (40)| 00:00:01 |
|   3 |    WINDOW SORT          |      |    14 |   546 |     5  (40)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | EMP  |    14 |   546 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

11 rows selected.

SQL>  


SY.
Previous Topic: Conditional Selection of a Field
Next Topic: Need sql
Goto Forum:
  


Current Time: Tue Sep 30 05:04:27 CDT 2014

Total time taken to generate the page: 0.13332 seconds