I'm using IDS 9.4 and am I'm looking to generate unique numbers in my TRANSSEQ column based on existing values in the TRANSNUMBER column.
The same TRANSNUMBER can appear multiple times, so I want the TRANSSEQ value to be the unique identifier within each TRANSNUMBER.
For example, TRANSNUMBER 1007291 is listed 3 times.
I'm using the below script to generate this TRANSSEQ value, 17291001.
SELECT SUBSTRING(transnumber FROM 4 FOR 4) / .001 + 10000001
does the numbers have to be within a specific range? If not then you could do a calculation including the rowid of the table. The rowid is an implicit column in every table and actually the primary index, therefore it's unique for every row in the table. So
We have a software bug in our corporate application, so I wrote some routines to work around this bug.
My fear is having the same data being loaded twice. I'm looking the to create the same TRANSSEQ values for the TRANSNUMBER everytime the query is run.
My temp table is where I want to create the unique TRANSSEQ value. Once that's done all of the data from the temp table will get loaded into a live table with a series of primary keys to prevent duplicate records.
select min(rowid) minrow,transnumber from
table1 group by transnumber
into temp table2 with no log;
select b.transnumber,substr(b.transnumber,4,4)::integer/0.001 +(10000001+ ((b.rowid-a.minrow))) as transseq
from table2 a,table1 b
into temp table3 with no log;