drop table sales
/
create table sales
as
select 'paper' product, 2006 year, 2 amount from dual union all
select 'pen' product, 2006 year, 80 amount from dual union all
select 'staples' product, 2006 year, 18 amount from dual union all
select 'paper' product, 2007 year, 4 amount from dual union all
select 'pen' product, 2007 year, 100 amount from dual union all
select 'staples' product, 2007 year, 30 amount from dual
/

select product
     , year
     , amount
  from sales
 model
       partition by (product)
       dimension by (year)
       measures (amount)
       rules
       ( amount[2008] = amount[2007] * amount[2007] / amount[2006]
       )
 order by year
     , product
/

create table t (id,str)
as
select 1, 'OGh Visie' from dual union all
select 2, 'Oracle Gebruikersclub Holland' from dual union all
select 3, null from dual union all
select 4, 'OGh' from dual
/

select id
     , i seqnr
     , str word
  from t
 model
       return updated rows
       partition by (id)
       dimension by (0 i)
       measures (str)
       ( str[for i from 1 to regexp_count(str[0],' ')+1 increment 1]
         = regexp_substr(str[0],'[^ ]+',1,cv(i))
       )
 order by id
     , seqnr
/

select id
     , n seqnr
     , regexp_substr(str,'[^ ]+',1,n) word
  from t
     , ( select level n
           from ( select max(regexp_count(str,' '))+1 max_#words
                    from t
                )
        connect by level <= max_#words
       )
 where n <= regexp_count(str,' ')+1
 order by id
     , seqnr
/

create table deposits
as
select 1 customer, 1000 amount, date '2003-01-01' deposit_date from dual union all
select 1 customer,  200 amount, date '2004-01-01' deposit_date from dual union all
select 1 customer,  500 amount, date '2005-01-01' deposit_date from dual union all
select 1 customer,  100 amount, date '2006-01-01' deposit_date from dual union all
select 1 customer,  800 amount, date '2007-01-01' deposit_date from dual union all
select 2 customer,   20 amount, date '2003-01-01' deposit_date from dual union all
select 2 customer,  150 amount, date '2004-01-01' deposit_date from dual union all
select 2 customer,   60 amount, date '2005-01-01' deposit_date from dual union all
select 2 customer,  100 amount, date '2006-01-01' deposit_date from dual union all
select 2 customer,  100 amount, date '2007-01-01' deposit_date from dual
/

select * from deposits
/

create table interest_rates
as
select date '2003-01-01' startdate,   5 percentage from dual union all
select date '2004-01-01' startdate, 3.2 percentage from dual union all
select date '2005-01-01' startdate, 4.1 percentage from dual union all
select date '2006-01-01' startdate, 5.8 percentage from dual union all
select date '2007-01-01' startdate, 4.9 percentage from dual
/

select * from interest_rates
/

select customer
     , amount
     , deposit_date
     , percentage
     , balance balance_at_end_of_year
  from deposits s
     , interest_rates r
 where s.deposit_date = r.startdate
 model
       partition by (s.customer)
       dimension by (s.deposit_date)
       measures (s.amount, r.percentage, 0 balance)
       rules
       ( balance[any] order by deposit_date
         = round
           (   (nvl(balance[add_months(cv(),-12)],0) + amount[cv()])
             * (1 + percentage[cv()]/100)
           , 2
           )
       )
 order by customer
     , deposit_date
/

select deptno
     , rtrim(ename,',') enames
  from ( select deptno
              , ename
              , rn
           from emp
          model
                partition by (deptno)
                dimension by (row_number() over
                              (partition by deptno order by ename) rn
                             )
                measures     (cast(ename as varchar2(40)) ename)
                rules
                ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
                )
       )
 where rn = 1
 order by deptno
/