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

# how to trigger renumbering of rows in a group?

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:

• ###### 1. Re: how to trigger renumbering of rows in a group?
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?
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?
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?
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?
Currently Being Moderated

Hi Frank,

Johan

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

Hi Etbin,

Johan

• ###### 7. Re: how to trigger renumbering of rows in a group?
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?
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?
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?
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?
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?
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