SQL Sequences and Loops

[jimmy80]

Hi,

I have a list of order ids in a table, there may be more than one of the same order ids, i want to set a variable equal to the order_id plus a sequence so basically if I have a list of order id in a table as follows

1234
1234
1234
9876
9876
6789

I want to set another field in the table equal to the following:
12341
12342
12343
98761
98762
67891
and so on.

I have tried setting up for loops but am getting a bit confused and was hoping someone might be able to help!!

Thanks
Jimmy

 

[jad]

if there are a variable amount of order ID's i wouldn't do sequences, i would also store the values as seperate fields rather than a combined if i could (>9 orders would cause trouble).

assuming that the data is coming in from a cursor:

declare
    cursor c_vals is
        select val_id
            from table_1;
    new_num integer := 0;
begin
for each_val in c_vals loop
    select count(val_id)+1 into new_num
        from table_2
        where floor(val_id/10) = each_val.val_id;
    insert into table_2 (val_id)
        values ((each_val.val_id *10)+new_num);
end loop;
/

 

[sem]

Suppose you have another field item_id in that table, and need to sort records within the same order_id by this field:

select order_id || row_number() over (partition by order_id order by item_id)
from order_items

Regards, Dima

 

[sem]

Of course, you may use

select order_id || row_number() over (partition by order_id order by order_id)
from order_items

but I suppose that in this case records are indistingushable thus it makes no sence.


Regards, Dima