Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    21

    Unanswered: IDS 9.4 - Creating unique values

    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!

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  3. #3
    Join Date
    May 2008
    Posts
    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.

  4. #4
    Join Date
    Aug 2005
    Posts
    140
    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:
    select * from table3
    transnumber transseq
    ------------------------------
    1007291 17291001
    1007292 17292002
    1007291 17291002
    1007291 17291003
    1007292 17292001

  5. #5
    Join Date
    May 2008
    Posts
    21
    Thank you Stan!

    I just a few tests on some sample data and everything works great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •