Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to write a 10G model clause for this

user517698Jun 14 2009 — edited Jun 17 2009
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

Comments

Toon Koppelaars
<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
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
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
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
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
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
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
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
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
user517698
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
user517698
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
Aketi Jyuuzou
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));
Aketi Jyuuzou
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;
user517698
Hi All ,

Tk you all for all the help given.

The examples are already very close to what i wanted

rgds
1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 15 2009
Added on Jun 14 2009
14 comments
2,857 views