12 Replies Latest reply on Sep 28, 2013 6:52 PM by EdStevens

    how to trigger renumbering of rows in a group?

    Johan 12045295

      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

           

          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?
            Johan 12045295

            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

              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

                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

                1 person found this helpful
                • 5. Re: how to trigger renumbering of rows in a group?
                  Johan 12045295

                  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?
                    Johan 12045295

                    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

                      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

                        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?
                          Johan 12045295

                          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

                            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?
                              Johan 12045295

                              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

                                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.