Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Question Unanswered: Update on group change

    Hi friends,

    I need your help to update the table.

    I have table called on_change

    f1 number(5),
    f2 number(5),
    f3 number(5),
    f4 number(5)

    my data is look like as below:

    F1 F2 F4
    ---------- ---------- ----------
    1 0 0
    1 0 3
    1 0 7
    1 1 1
    1 1 2
    1 1 3
    1 2 0
    1 2 1
    1 2 2
    1 2 3
    1 2 4
    1 7 0
    1 7 1
    1 7 2
    1 7 3
    1 15 1
    1 15 2
    1 15 3
    1 15 4
    2 0 0
    2 0 3
    2 0 7
    2 1 1
    2 1 2
    2 1 3
    2 1



    I want to update on_change table in following way:

    - first I want order in f1, f2, f4 with group f1+f2
    - when f1 +f2 changes I want to update f4 with 1,2,3,4

    Expected result of above data should be as below:


    F1 F2 F4
    ---------- ---------- ----------
    1 0 1
    1 0 2
    1 0 3

    1 1 1
    1 1 2
    1 1 3

    1 2 1
    1 2 2
    1 2 3
    1 2 4
    1 2 5

    1 7 1
    1 7 2
    1 7 3
    1 7 4

    1 15 1
    1 15 2
    1 15 3
    1 15 4

    2 0 1
    2 0 2
    2 0 3

    2 1 1
    2 1 2
    2 1 3


    The idea is to update F4 when there is change in f1 and f2 with incremental value 1,2,3....

    Can anybody suggest me possible pl-sql block for this?

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    easiest way is to write a pl/sql block

    take the records into a cursor

    select * from ch_table order by F1,F2


    then in teh loop check the value of the prev with current

    if changed update with the running slno.
    else reset the slno.

    Hope this helps

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    Hi friend,

    Thanks for the reply.
    I achinved it using following query.


    select f1,f2 ,
    (row_number() over (partition by f1,f2 order by f1,f2) ) newvalue
    from t1;


    Thanks oncegain.

    Rajendra

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •