IDS 9.4 - Creating unique values

skisalomon77 · #1 (**permalink**) 06-02-09, 17:02

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.

TRANSNUMBER
1007291
1007291
1007291

I'm using the below script to generate this TRANSSEQ value, 17291001.

SELECT SUBSTRING(transnumber FROM 4 FOR 4) / .001 + 10000001

Currently, my columns looks like this...

TRANSNUMBER TRANSSEQ
1007291 17291001
1007291 17291001
1007291 17291001

I want them to look like this...

TRANSNUMBER TRANSSEQ
1007291 17291001
1007291 17291002
1007291 17291003

Any help in the right direction would be greatly appreciated!

Tyveleyn · #2 (**permalink**) 06-02-09, 18:03

Hi,

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

Code:

SELECT substr(transnumber, 4, 4)::INTEGER + rowid ...

shall give a unique number if the results of the subtring function are equal.

Regards,
Hans

skisalomon77 · #3 (**permalink**) 06-02-09, 23:21

Thanks for the quick response Hans!

I tried your solution and I was able to obtain a unique number in the TRANSSEQ column.

I was hoping to get the data to look like this though...

TRANSNUMBER TRANSSEQ
1007291 17291001
1007291 17291002
1007291 17291003

...and I'll explain why.

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.

stanislav.ondac · #4 (**permalink**) 06-03-09, 06:07

Hi...try something like this:

Code:

select * from table1

transnumber
----------------
1007291
1007291
1007291
1007292
1007292

Code:

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
where a.transnumber=b.transnumber
into temp table3 with no log;

Code: