SQL & PL/SQL

<pre>with TAB as (
select 1 id, 8 working, 0 working_perc from dual
union all
select 2,8,0 from dual
union all
select 3,8,0 from dual
union all
select 4,8,0 from dual
union all
select 5,0,0 from dual
union all
select 6,0,0 from dual
union all
select 7,8,0 from dual
union all
select 8,8,0 from dual
union all
select 9,8,0 from dual)
select id
,working
,working_perc
from TAB
model
dimension by (id)
measures (working, working_perc)
rules (working_perc\[id>1\] =
case working\[cv(id)-1\]
when 0 then 0
else working_perc\[cv(id)-1\] + working\[cv(id)-1\]
end
)
/

ID WORKING WORKING_PERC
---------- ---------- ------------
1 8 0
2 8 8
3 8 16
4 8 24
5 0 32
6 0 0
7 8 0
8 8 8
9 8 16</pre>

Edited by: Toon Koppelaars on Jun 15, 2009 8:53 AM

Why do you "need" the MODEL clause?

With your limited info you can do something like

select work_perc + 
        case 
          when lead (failure_reason) over (partition by mch_id
                              order by dt
                         ) is not null
          then 8
          else 0
       end
  from test

like in

SQL> with test as
  2  (
  3  select 1 mch_id, to_date ('6/1/2009', 'mm/dd/yyyy') dt, '' failure_reason,  8 hrs,  0  work_perc from dual union all
  4  select 1 mch_id, to_date ('6/2/2009', 'mm/dd/yyyy') dt, '' failure_reason,  8 hrs,  8  work_perc from dual union all
  5  select 1 mch_id, to_date ('6/3/2009', 'mm/dd/yyyy') dt, '' failure_reason,  8 hrs,  16 work_perc from dual union all
  6  select 1 mch_id, to_date ('6/4/2009', 'mm/dd/yyyy') dt, '' failure_reason,  8 hrs, 24  work_perc from dual union all
  7  select 1 mch_id, to_date ('6/5/2009', 'mm/dd/yyyy') dt, 'Hydraulic'      , 0 hrs,  32 work_perc from dual union all
  8  select 1 mch_id, to_date ('6/6/2009', 'mm/dd/yyyy') dt, 'Hydraulic'      , 0 hrs,  0  work_perc from dual union all
  9  select 1 mch_id, to_date ('6/7/2009', 'mm/dd/yyyy') dt, '' failure_reason, 8 hrs,  0  work_perc from dual union all
 10  select 1 mch_id, to_date ('6/8/2009', 'mm/dd/yyyy') dt, '' failure_reason, 8 hrs,  8  work_perc from dual union all
 11  select 1 mch_id, to_date ('6/9/2009', 'mm/dd/yyyy') dt, '' failure_reason, 8 hrs,  16 work_perc from dual
 12  )
 13  select mch_id
 14       , dt
 15       , failure_reason
 16       , hrs
 17       , work_perc
 18       , work_perc + 
 19          case 
 20            when lead (failure_reason) over (partition by mch_id
 21                                order by dt
 22                           ) is not null
 23            then 8
 24            else 0
 25         end work_perc
 26    from test
 27  /

    MCH_ID DT        FAILURE_R        HRS  WORK_PERC  WORK_PERC
---------- --------- --------- ---------- ---------- ----------
         1 01-JUN-09                    8          0          0
         1 02-JUN-09                    8          8          8
         1 03-JUN-09                    8         16         16
         1 04-JUN-09                    8         24         32
         1 05-JUN-09 Hydraulic          0         32         40
         1 06-JUN-09 Hydraulic          0          0          0
         1 07-JUN-09                    8          0          0
         1 08-JUN-09                    8          8          8
         1 09-JUN-09                    8         16         16

9 rows selected.

hi,

what if the dimension is more than just id column

it has id , dept , supervisor in order to be unique for the day ?

how can i actually use the cell reference like in excel

i tried reading some examples but it mostly refering to a specific product & year which in my cause i do not want to be limited by some specific values ?


tks & rgds

Did you got the point which Alex raised?

When your solution can be done in that way why do you require model clause? Is there any specific reason to strict with the MODEL Clause?

Regards.

Satyaki De.

Thanks for the backup Satyaki De. :) , but my results are different from the one Toon has... so I think my query is not correct.

Not sure what you mean, but you can do stuff like this:

<pre>
model
dimension by (day,id,dept,supervisor)
measures (working, working_perc)
rules (working_perc\[day,id>1,dept,supervisor\] =
... rules ...
)
</pre>

Hi ,

with my initial understanding , the examples i have shown is quite simple which is going to be more complex , have tried the partition by it need several query over query and that has affected the performance ,
so i am trying out the model clause

tks & rgds

I got it.

I thought Alex's solution is working and that was so simple - so i was saying about that approach which is very simple and effective - if it produce the same result.

Regards.

Satyaki De.

I solved :D

with WorkView as(
select 1 as sortKey,1 as val from dual union
select 2,2 from dual union
select 3,4 from dual union
select 4,8 from dual union
select 5,0 from dual union
select 6,0 from dual union
select 7,2 from dual union
select 8,4 from dual union
select 9,8 from dual)
select *
  from WorkView
 model
dimension by(sortKey)
measures(val,0 as sumV)
rules(
sumV[sortKey >= 2] order by sortKey
= case val[cv()-1] when 0 then 0
  else sumV[cv()-1]+val[cv()-1] end);

sortKey  val  sumV
-------  ---  ----
      1    1     0
      2    2     1
      3    4     3
      4    8     7
      5    0    15
      6    0     0
      7    2     0
      8    4     2
      9    8     6

Hi ,

yes what you have stated is something that i am looking at



however, can i actually specify the columns i mentioned in the dimension clause at the SELECT clause ?
actually my dimension column uses quite a large no of fields in order to be unique set

but
i keep getting the duplicate columns and in the end i am only left with 2 columns in the SELECT clause which i actually need to show much more columns

tks & rgds

hi ,

i managed to resolve the duplicate column name(my mistake for really using the column name twice) but i still have this not enough values error

i would thought that whatever fields i specifed in the dimension clause , if i specifies in the rules , it should be ok

select seqnum , id , time_in , time_out, ct , proj
from

MYTABLE

)
model
dimension by (seqnum, id)
measures (ct , time_in , time_out , sysdate as proj)
rules (
proj[seqnum=1,id] = case time_in[seqnum=1,id,cv()]
when null then sysdate
else
time_in[cv()]
end
)


it says that time_in[seqnum=1,id,cv()] has not enough values , what might be the problem here ?

rgds

other solution1

select sortKey,Val,sum(LagVal) over(partition by PID order by sortKey) as sumV
from (select sortKey,Val,LagVal,
      sum(decode(LagVal,0,1,0)) over(order by sortKey) as PID
      from (select sortKey,Val,Lag(Val,1,0) over(order by sortKey) as LagVal
            from WorkT));

other solution2

select sortKey,Val,
nvl(sum(Val) over(partition by PID
                  order by sortKey rows between unbounded preceding
                                            and 1 preceding),0) as sumV
from (select sortKey,Val,
      Last_Value(decode(Val,0,sortKey) ignore nulls)
      over(order by sortKey rows between unbounded preceding
                                     and 1 preceding) as PID
        from WorkT)
order by sortKey;

Hi All ,

Tk you all for all the help given.

The examples are already very close to what i wanted

rgds