This discussion is archived
12 Replies Latest reply: Sep 28, 2013 11:52 AM by EdStevens RSS

how to trigger renumbering of rows in a group?

user12045295 Newbie
Currently Being Moderated

Suppose we have a table ORDER_DETAILS with columns (ORDER_ID, LINE_NUMBER,DESCRIPTION).

We have these rows:

 

order_id, line_number, description

17,1,'A'

17,2,'G'

17,3,'R'

17,4,'Q'

 

Next we insert a new row

17,2,'K'

 

How to trigger renumbering?

 

This should be the result after renumbering.

17,1,'A'

17,2,'K'

17,3,'G'

17,4,'R'

17,5,'Q'

 

Next we move line 5 to line 2.

 

How to trigger renumbering?

 

This should be the result after renumbering.

17,1,'A'

17,2,'Q'

17,3,'K'

17,4,'G'

17,5,'R'

 

This question is related to another discussion:

https://forums.oracle.com/thread/1088303

  • 1. Re: how to trigger renumbering of rows in a group?
    Ashu_Neo Pro
    Currently Being Moderated

     

    How to trigger renumbering?

    Next we move line 5 to line 2.

    How to trigger renumbering?

     

    Re-numbering(re-ordering) based upon what? Like :- Moving 2nd line to 5th, why not to 3rd or 4th?

  • 2. Re: how to trigger renumbering of rows in a group?
    user12045295 Newbie
    Currently Being Moderated

    Hi,

     

    Re-numbering is based on the number of a line.

    For example, a user decides to insert a new line with line number 2. This user action should trigger re-numbering.

    Alternatively, a user can decide to move position of line number 5 to position 2 (update order_details set line_number = 2 where line_number = 5 and order_id = 17). This update should trigger re-numbering.

     

    Did I make myself clear?

  • 3. Re: how to trigger renumbering of rows in a group?
    Etbin Guru
    Currently Being Moderated

    create table order_details

    (order_id number,

    line_number number,

    description varchar2(30)

    )

    rowdependencies;

     

    begin

    insert into order_details values (17,1,'A');

    insert into order_details values (17,2,'G');

    insert into order_details values (17,3,'R');

    insert into order_details values (17,4,'Q');

    end;

     

    select * from order_details order by line_number

     

    ORDER_IDLINE_NUMBERDESCRIPTION
    171A
    172G
    173R
    174Q

     

    create or replace procedure renumber(p_order_id number) as

    begin

      update order_details od

         set line_number = (select rn

                              from (select rowid rid,

    /* for autocommit active APEX etc. */  row_number() over (order by line_number,ora_rowscn desc) rn

    /* for autocommit = OFF  preferred     row_number() over (order by line_number,ora_rowscn nulls first) rn */

                                      from order_details

                                     where order_id = p_order_id

                                   )

                             where rid = od.rowid

                           )

       where order_id = p_order_id;

    end;

     

    begin

    insert into order_details values (17,2,'K');

    renumber(17);

    end;


    select * from order_details order by line_number


    ORDER_IDLINE_NUMBERDESCRIPTION
    171A
    172K
    173G
    174R
    175Q

     

    begin

    update order_details

        set line_number = 2

      where order_id = 17

        and line_number = 5;

     

    renumber(17);

    end;

     

    select * from order_details order by line_number

     

    ORDER_IDLINE_NUMBERDESCRIPTION
    171A
    172Q
    173K
    174G
    175R

     

    Regards

     

    Etbin

  • 4. Re: how to trigger renumbering of rows in a group?
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    You can do that with the analytic ROW_NUMBER function; no PL/SQL is needed.

     

    If you'd like help, post a little sample data (CREATE TABLE and INSERT statements for a case where the line number needs to be fixed) and the results you want from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 5. Re: how to trigger renumbering of rows in a group?
    user12045295 Newbie
    Currently Being Moderated

    Hi Frank,

     

    Thanks for your answer.

     

    I've learnt a lot reading your link to the FAQ.

     

    Johan

  • 6. Re: how to trigger renumbering of rows in a group?
    user12045295 Newbie
    Currently Being Moderated

    Hi Etbin,

     

    Thanks for your comprehensive answer. Your solution really solves my problem.

     

    Johan

  • 7. Re: how to trigger renumbering of rows in a group?
    EdStevens Guru
    Currently Being Moderated

    Coming a bit late to the thread, but I can't help but wonder why you'd want to change the line numbers of order line details in the first place?  When I last worked in apps, my big system was an inventory control/PO system, and a line number of an PO detail line was considered immutable.  If I look at a PO today and see that line one is for the 30 each of the 3/4" widget,  I don't want to talk to someone tomorrow and have them thinking line one is for 12 ea. of the Left Hand Smoke Shifter.

     

    What business problem is solved by allowing the re-ordering of po lines?

     

     

    Unless the PO was just a working example of some other application, I think your 'requirement' needs to be re-considered.

  • 8. Re: how to trigger renumbering of rows in a group?
    Etbin Guru
    Currently Being Moderated

    More than 30 years ago I had to produce a listing of a kind of financial plan - some 50 copies maybe (it was in the selfmanagement era).

    I was being yelled at because I didn't print zero valued accounts as there were plenty of them so I thought it was appropriate to save some paper.

    The explanation (they took it as a valid business problem for sure - I was the only one not to grasp it thinking everybody involved must know what (s)he was doing) was:

    The general manager is convinced the only way to keep the meeting proceed with an acceptable pace is scanning the plan saying: the amount on page p, line l has to be decreased by amount a. Lets hear from those of you who have nonzero figures there what can be your contribution to the objective to be reached.

     

    Regards

     

    Etbin

  • 9. Re: how to trigger renumbering of rows in a group?
    user12045295 Newbie
    Currently Being Moderated

    Hi Ed,

     

    As you mentioned in the end, it is just a working example. I suppose that the table ORDER_DETAILS is common and also simulates my issue.


    The real requirement is about a table TEST_ACTIONS. The order in which to execute the test actions is a relevant component of the test description. It should be possible to improve a test by adding in between and re-sequence test actions.

     

    I am open to any suggestion that can improve our design.

     

    Johan

  • 10. Re: how to trigger renumbering of rows in a group?
    Frank Kulash Guru
    Currently Being Moderated

    Hi, Johan,

     

    user12045295 wrote:

     

    ...

     

    I am open to any suggestion that can improve our design.

     

    Johan

    The is no such thing as a best design, or even a good design.  There is, however, such a thing as a good design for a given purpose, or for a set of purposes.  For what purpose(s) are you designing?  It's easy to see why arranging rows in order of description could be useful; that's what   ORDER BY description  does.  Why do you need a second column to duplicate that order?  What purpose is served by making that column a NUMBER?  What does it need to be all integers?  Why do they have to be consecutive integers?

     

    If you can say what problem you're trying to solve, or what expected problem you're trying to avoid, then someone can help you find a good way to do it in Oracle.

    I can imagine good reasons for having a line_number column that is always consecutive integers, starting with 1, but I have no idea if that's what you really need or not.

  • 11. Re: how to trigger renumbering of rows in a group?
    user12045295 Newbie
    Currently Being Moderated

    Hi Frank,

     

    For what purpose(s) are you designing?

     

    The real requirement is about a table TEST_ACTIONS. The order in which to execute the test actions is a relevant component of the test description. To execute a test you perform action 1, then action 2, then action 3 and finally action 4. It is wrong to perform actions in a different order. It should be possible to improve a test by adding in between and re-sequence test actions.

    Why do you need a second column to duplicate that order?

    We don't need a column to duplicate the order.

    What purpose is served by making that column a NUMBER?  What does it need to be all integers?  Why do they have to be consecutive integers?

    We don't need consecutive integers. The action should display in the proper order. So for example action A comes before action B comes before action C comes before action D is acceptable.


    Johan

  • 12. Re: how to trigger renumbering of rows in a group?
    EdStevens Guru
    Currently Being Moderated

    user12045295 wrote:

     

    Hi Ed,

     

    As you mentioned in the end, it is just a working example. I suppose that the table ORDER_DETAILS is common and also simulates my issue.


    The real requirement is about a table TEST_ACTIONS. The order in which to execute the test actions is a relevant component of the test description. It should be possible to improve a test by adding in between and re-sequence test actions.

     

    I am open to any suggestion that can improve our design.

     

    Johan

     

    Ah, for that, needing to manually sequence test steps as the overall test plan is developed .. now we have a legit business case and are back to finding the best technical solution.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points