Forum Stats

  • 3,767,858 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

how to write a 10G model clause for this

user517698
user517698 Member Posts: 939
edited Jun 17, 2009 9:21AM in SQL & PL/SQL
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
«1

Answers

  • Toon Koppelaars
    Toon Koppelaars Member Posts: 2,607
    edited Jun 15, 2009 2:53AM
    <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
    Toon Koppelaars
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    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.
    21205
  • user517698
    user517698 Member Posts: 939
    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
    Satyaki_De Member Posts: 7,081
    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
    21205 Member Posts: 6,168 Gold Trophy
    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
    Toon Koppelaars Member Posts: 2,607
    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
    user517698 Member Posts: 939
    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
    Satyaki_De Member Posts: 7,081
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jun 16, 2009 6:21AM
    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
    user517698 Member Posts: 939
    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
This discussion has been closed.