Forum Stats

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

Discussions

how to write a 10G model clause for this

2»

Answers

  • user517698
    user517698 Member Posts: 939
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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;
    Aketi Jyuuzou
  • user517698
    user517698 Member Posts: 939
    Hi All ,

    Tk you all for all the help given.

    The examples are already very close to what i wanted

    rgds
This discussion has been closed.