Step 1: Create Test Table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
truncate table test_table; drop table test_table; create global temporary table test_table ( txn_no number(4,0) ,txn_sq number(3,0) ) on commit preserve rows; commit; insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1003); insert into test_table (txn_no) values (1003); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1003); commit; |
Result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT * from test_table -------------------------- TXN_NO TXN_SQ ------ ------ 1001 1001 1001 1001 1002 1002 1002 1003 1003 1001 1002 1003 12 rows selected |
Step 2: Run PL/SQLl anonymous block to deal with the duplicates:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
declare var_txn_no test_table.txn_no%type; -- variable for transaction number var_txn_sq test_table.txn_sq%type; -- variable for our own per txn_no sequence that we are going to insert var_txn_no_pr test_table.txn_no%type; -- variable to track value of previous txn_no i integer; -- iterator cursor curs_1 is select txn_no from test_table order by txn_no for update of txn_sq; begin var_txn_no_pr := 0; open curs_1; loop fetch curs_1 into var_txn_no; exit when curs_1%notfound; if var_txn_no = var_txn_no_pr then i := (i + 1); else i := 1; end if; update test_table set txn_sq = i where current of curs_1; var_txn_no_pr := var_txn_no; end loop; close curs_1; commit; end; / |
Result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT * FROM TEST_TABLE order by TXN_NO, TXN_SQ ----------------------------------- TXN_NO TXN_SQ ------ ------ 1001 1 1001 2 1001 3 1001 4 1001 5 1002 1 1002 2 1002 3 1002 4 1003 1 1003 2 1003 3 12 rows selected |