how to write a 10G model clause for this

user517698 · Jun 14, 2009 9:31PM

Hi ,

I am using Oracle 10G and i am trying to use the MODEL clause to cater for the following logics

data :

Row MCH_ID Date Failure_reason Working_hour(hrs) Working%
1 1 6/1/2009 8 0
2 1 6/2/2009 8 8
3 1 6/3/2009 8 16
4 1 6/4/2009 8 24
5 1 6/5/2009 Hydraulic 0 32
6 1 6/6/2009 Hydraulic 0 0
7 1 6/7/2009 8 0
8 1 6/8/2009 8 8
9 1 6/9/2009 8 16

Logics :
1 - 1st row will be 0 for working% & failure%
2 - if machine is working the next day , working% will add 8 hours
3 - if machine is down then the next day, the working% will be reset to 0%

pls advise

tks & rgds

Edited by: user517698 on Jun 14, 2009 7:02 PM

Toon Koppelaars · Jun 15, 2009 2:51AM

<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

21205 · Jun 15, 2009 2:55AM

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.

user517698 · Jun 16, 2009 2:40AM

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

Satyaki_De · Jun 16, 2009 2:42AM

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.

21205 · Jun 16, 2009 3:07AM

Satyaki_De wrote:
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.

Toon Koppelaars · Jun 16, 2009 4:26AM

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>

user517698 · Jun 16, 2009 5:49AM

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

Satyaki_De · Jun 16, 2009 5:51AM

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.

Aketi Jyuuzou · Jun 16, 2009 6:20AM

I solved

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

user517698 · Jun 16, 2009 6:21AM

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

Forum Stats

Quick Links

Categories

how to write a 10G model clause for this

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

how to write a 10G model clause for this

Answers