Generating Alphanumeric sequence using DS

[tan1111]

Dear All,
I have a requirement of generating a alphanumeric sequence which looks like AA,AB...AZ,A0,A1...A9,BA...BZ,B0...B9...up to 99 where all the alphabets become 9.
So basically the 2nd character increases and when it becomes Z the number is reset to 0 and it goes on until 9, and then the 1st char increase one character and so on.
I hope I could explain the flow of the sequence. I need some ideas on how to implement such thing using Data stage as I am pretty new to the tool.
Thanks

[FranklinE]

Your sequence is what Excel uses to name columns, with the addition by you of the letter-numeral sequence between the end of one set and the beginning of the next.

I would question that addition. It breaks the pattern of the letter sequence.

Anyway, I don't know what algorithm Excel uses for its column headers, but it shouldn't be that difficult to work out a formula that represents base-26, meaning you have 26 "digits" to run through in each numeral position. I suppose it would then be possible to insert the letter-numeral section.

The simple, inelegant method is to create a table with the sequence, and read it. No extra coding other than to keep track of where you are in the table.

[qt_ky]

With the digits added, it goes to base 36. If you wanted a more programmatic approach, you could do some simple looping (i.e. from 1 to 72) and convert the integer to your custom sequence value by using some logic like Mod() function, If Then Else statements, and the Char() function. Sounds like a good morning brain warm up exercise.

[FranklinE]

With the digits added, it goes to base 36.

Missed how the numerics extend the pattern. Good catch.

[qt_ky]

Thanks.

I suppose if the goal is only to build a 2-character wide ordered list, you would need to loop 1296 times, which would cover all possible values (36*36=1296).

[UCDI]

make an array of the digits you want.

make a sequence of base 10 values (0, 1, ... N)

you should be able to convert the base 10 into a string from there using either the change of base formula (logs) and some rounding to ensure integers or you can probably just do it with division & remainders (mod).

Basically you need to get one base 36 digit, look up the letter in your table, and cook up a string that way digit by digit, or do similar thing via a looping and tracking algorithm.

[SURA]

I am not sure whether this will work for you or not, but still it may help for someone.

This solution is using Oracle, but you will get the same output. Then up to you to decide whether you need to use it or not!

Code: Select all

WITH data AS (SELECT Chr(65 + LEVEL - 1) str FROM   dual CONNECT BY LEVEL <= 99) SELECT str ||column_value AS vVal FROM   data, TABLE(Cast(MULTISET (SELECT LEVEL FROM   dual CONNECT BY LEVEL < 99 + 1) AS sys.ODCINUMBERLIST))
where rownum < 2575
union all
SELECT vVal FROM (select chr(level + 65 - 1) pos1 from dual connect by level <= 26) MODEL DIMENSION BY (pos1 AS x, 'A' as y) MEASURES ('--' AS vVal) RULES UPSERT ALL ITERATE (26) ( vVal[any, chr(iteration_number + 65)] = (CV(x) || CV(y)));

Please test it before use.