set echo on
-- hello world
select * from dual
model
dimension by ('Hello' x)
measures ('World' y) ();
pause
-- SAL['name']
SELECT * FROM EMP MODEL 
DIMENSION BY (ENAME) 
MEASURES (SAL)
(
    UPDATE SAL[ENAME='SCOTT']=4000,
    -- UPDATE SAL['SCOTT']=4000,
    UPDATE SAL[ENAME='JAMES']=SAL[ENAME='KING'],
    UPDATE SAL[ENAME IN ('BLAKE','CLARK')]=2500,
    UPSERT SAL['QUEEN']=8000
);


pause
-- SAL['name'] sequential order
SELECT * FROM EMP MODEL 
DIMENSION BY (ENAME) 
MEASURES (SAL)
RULES SEQUENTIAL ORDER
(
    SAL['SCOTT']=4000,
    SAL[ANY]=SAL[CV()]+10
);


pause
-- SAL['name'] automatic order
SELECT * FROM EMP MODEL 
DIMENSION BY (ENAME) 
MEASURES (SAL)
RULES AUTOMATIC ORDER
(
    SAL['SCOTT']=4000,
    SAL[ANY]=SAL[CV()]+10
);


pause
-- PARTITION value is a constant
SELECT  ENAME, SAL FROM EMP MODEL
PARTITION BY (0.03 BONUS)
DIMENSION BY (ENAME)
MEASURES (SAL)
(SAL ['SCOTT']=SAL[CV()]*(1+CV(BONUS)));
pause
-- partition on a column
SELECT DEPTNO, ENAME, SAL, BONUS FROM EMP MODEL
UNIQUE SINGLE REFERENCE
PARTITION BY (DEPTNO)
DIMENSION BY (RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) R)
MEASURES (ENAME, SAL, 0 BONUS, COUNT(*) OVER (PARTITION BY DEPTNO, SAL) C)
(BONUS[1]=100/MAX(C)[1])
order by deptno, sal desc;

pause
-- partition on a column
SELECT DEPTNO, ENAME, SAL FROM EMP MODEL
PARTITION BY (DEPTNO)
DIMENSION BY (ENAME)
MEASURES (SAL)
(SAL['SCOTT']=100,
SAL[ENAME='KING']=200
)
order by deptno, sal desc;

pause
-- a measure initialized by a constant
SELECT  DNAME, X FROM DEPT MODEL
DIMENSION BY (DNAME)
MEASURES (1 X)
RULES ITERATE (20)
(X['SALES']=X[CV()]*2);

pause
-- a measure initialized by a column
SELECT  DNAME, LOC FROM DEPT MODEL
DIMENSION BY (DNAME)
MEASURES (LOC)
(LOC['SALES']='SF');
pause
-- an array of values initialized in a reference model
SELECT E FROM DUAL MODEL
REFERENCE M ON 
(SELECT ROWNUM R, ENAME FROM EMP)
DIMENSION BY (R)
MEASURES (ENAME)
DIMENSION BY (DUMMY)
MEASURES ('xxxxxxxxxx' E)
(E['X']=M.ENAME[7]);

pause
-- the whole is an array
SELECT * FROM DEPT MODEL 
DIMENSION BY (DEPTNO D) 
MEASURES (DNAME M) (M[1]='FOO', M[10]='BAR') 
ORDER BY D;

pause
-- condition on the left hand side
SELECT DUMMY FROM DUAL MODEL
DIMENSION BY (0 X, 0 Y, 0 Z)
MEASURES (DUMMY)
(DUMMY[
X IS NOT NULL,
Y>=0,
Z IS ANY
]='Y');

pause
-- a condition on the right hand-side in a CASE function
SELECT DEPTNO, DNAME, NEW FROM DEPT MODEL
DIMENSION BY (DEPTNO) MEASURES (DNAME, '   ' NEW)
(DNAME[50]='MARKETING',
NEW[ANY]=CASE WHEN NEW[CV()] IS PRESENT THEN 'NO' 
  ELSE 'YES' END);

pause
-- iterate loop
col amount for 999G999G999G999G999G999G999G999
SELECT YEAR, AMOUNT FROM DUAL MODEL 
PARTITION BY (0.05 RATE) DIMENSION BY (1 YEAR) 
MEASURES (1 AMOUNT) 
RULES ITERATE (999)
(AMOUNT[ITERATION_NUMBER+2]=
   AMOUNT[ITERATION_NUMBER+1]*(1+CV(RATE)));

list
pause
-- iterate with a condition
SELECT DEPTNO,LOOPS FROM DEPT MODEL PARTITION BY (DEPTNO) 
DIMENSION BY (1 D) MEASURES (0 M, 0 LOOPS) 
RULES ITERATE (100) UNTIL PREVIOUS(M[1])=35 
(M[1]=ITERATION_NUMBER+1+CV(DEPTNO),
LOOPS[1]=ITERATION_NUMBER+1) ;

pause
-- a FOR loop for number
SELECT X FROM DUAL WHERE 1=2 MODEL
DIMENSION BY (0 X) MEASURES (0 Y)
(Y[FOR X FROM 0 TO 1 INCREMENT .25]=0);

pause
-- a FOR loop for number decrement
SELECT X FROM DUAL WHERE 1=2 MODEL
DIMENSION BY (0 X) MEASURES (0 Y)
(Y[FOR X FROM 400 TO 100 DECREMENT 100]=0);

pause
-- a for loop for dates
SELECT X FROM DUAL WHERE 1=2
MODEL
DIMENSION BY (TO_DATE(NULL) X)
MEASURES (0 Y)
(Y[FOR X FROM DATE '2000-01-01' TO DATE '2000-12-31' INCREMENT INTERVAL '3' MONTH]=0);
pause
-- a for loop for dates with sysdate that fails
SELECT X FROM DUAL WHERE 1=2
MODEL
DIMENSION BY (TO_DATE(NULL) X)
MEASURES (0 Y)
(Y[FOR X FROM TRUNC(SYSDATE) TO TRUNC(SYSDATE)+86399 INCREMENT INTERVAL '1' HOUR]=0);

pause
-- a for loop for dates using a reference model
SELECT X FROM DUAL WHERE 1=2
MODEL
REFERENCE R ON (
  SELECT 1 D,TRUNC(SYSDATE,'IW') B1, TRUNC(SYSDATE,'IW')+6 B2 FROM DUAL)
  DIMENSION BY (D)
  MEASURES (B1,B2)
DIMENSION BY (TO_DATE(NULL) X)
MEASURES (0 Y)
(Y[FOR X FROM b1[1] TO b2[1] INCREMENT 1]=0);

pause
-- a for loop for dates to count
SELECT * FROM EMP
GROUP BY TRUNC(HIREDATE,'Y')
MODEL
DIMENSION BY (TRUNC(hiredate,'Y') hiredate)
MEASURES (COUNT(*) COUNT)
(COUNT[
    FOR HIREDATE FROM DATE '1980-01-01' TO DATE '1990-01-01' INCREMENT INTERVAL '1' YEAR]=
  PRESENTV(COUNT[CV()],COUNT[CV()],0))
order by hiredate;
pause
-- a for loop with strings
SELECT X FROM DUAL WHERE 1=2 MODEL
DIMENSION BY ('ABC1' X) MEASURES (0 Y)
(Y[FOR X LIKE 'ABC%' FROM 1 TO 4 INCREMENT 1]=0);

pause
-- Aggregation
SELECT R, SAL FROM EMP MODEL RETURN UPDATED ROWS
DIMENSION BY (ROWNUM R, ENAME, JOB, DEPTNO)
MEASURES (SAL)
(SAL[-1,NULL,NULL,NULL]=
    SUM(SAL)[ANY,ANY,'CLERK',20],
SAL[-2,NULL,NULL,NULL]=
    SUM(SAL)[ANY,ENAME IN ('SCOTT','KING'),ANY,ANY]);

pause
select * from emp model dimension by (ename) measures (sal) (sal['WORLD']=SUM(SAL)[ANY])
/
pause
-- Analytics
SELECT ENAME, SAL, R FROM EMP WHERE DEPTNO=20 MODEL
DIMENSION BY (ENAME) MEASURES (SAL, TO_NUMBER(NULL) R)
(R[ENAME IN ('JONES','ADAMS','SMITH')]=
  RANK() OVER (ORDER BY SAL DESC)) 
  ORDER BY SAL DESC;
pause
-- identify employed with a salary greater than scott salary 
select * from emp model dimension by (ename) measures (sal, 'N' rich) (rich[ANY]=case when sal[cv()]>=sal['SCOTT'] then 'Y' else 'N' end)
/
pause
-- identify employed with a salary greater than scott salary 
select ename,sal from emp model
unique single reference
return updated rows
reference m on (select 1 n,sal from emp where ename='SCOTT')
  dimension by (n)
  measures (sal)
dimension by (sal)
measures (ename,0 dummy)
(dummy[sal>=m.sal[1]]=0)
/
pause
-- select ename and manager (with its name)
select empno, ename, mgr, manager from emp model dimension by (empno) measures (ename, mgr, ename manager) (manager[any]=ename[mgr[cv()]])
/
pause
select title, name from dual model
dimension by (cast(null as varchar2(10)) title)
measures (cast(null as varchar2(50)) name)
(
name['my book']='Advanced Oracle SQL Programming',
name['my email']='laurentschneidercat yahoo.com',.sql
name['my blog']='http://laurentschneider.com'
);