Informatica character sequence

Question

I am trying to create character sequence like AAA, AAB, AAC, AAD,....,BAA, BAB, BAC,.... and So on in a flat file using Informatica. I have the formula to create the charater sequence.

Here I need to have sequence numbers generated in informatica. But I dont have any source file or database to have this source.

Is there any method in Informatica to create sequence using Sequence Generater when there is no source records to read?

Answer 1

This is bit tricky as Informatica will do row by row processing and your mapping won't initialize until you give source rows through input(File or DB). So for generating sequence of n length by Informatica trnasformations you need n rows in input. Another soltion to this is to use Dummy Source(i.e. Source with one row) and you can pass the loop parameters from this source and then use Java transfornmation and Java code to generate this sequence.

Answer 2

There is no way to generate rows without a source in a mapping.

When I need to do that I use one of these methods :

• Generating a file with as many lines as I need, with the seq command under Unix. It could also be used as a direct pipeline source without creating the file.
• Getting lines from a database

For example Oracle can generate as many lines as you want with a hierarchical query :

SELECT LEVEL just_a_column
FROM dual
CONNECT BY LEVEL <= 26*26*26

DB2 can do that with a recursive query :

WITH DUMMY(ID) AS (
    SELECT 1 FROM SYSIBM.SYSDUMMY1    
    UNION ALL
    SELECT ID + 1 FROM DUMMY WHERE ID < 26*26*26
)
SELECT ID FROM DUMMY