Informatica character sequence
I am trying to create a sequence of characters like AAA, AAB, AAC, AAD, ...., BAA, BAB, BAC, .... etc. in a flat file using Informatica. I have a formula to create a charater sequence.
This is where I need to have ordinal numbers generated in computer science. But I don't have a source file or database to have this source.
Is there any method in Informatica to create a sequence using Generation Generation when there are no original records to read?
source to share
There is no way to generate strings without source in collation.
Whenever I need to do this, I use one of these methods:
- Creating a file with as many lines as I need with a command
seq
under Unix. It can also be used as a direct pipeline source without creating a file. - Retrieving rows from the database
For example, Oracle can generate as many rows as you like with a hierarchical query:
SELECT LEVEL just_a_column
FROM dual
CONNECT BY LEVEL <= 26*26*26
DB2 can do this 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
source to share
This is a bit tricky as Informatica will do the processing line by line and your mapping will not be initialized until you specify the original lines through the input (File or DB). Thus, to generate a sequence of n length according to Informatica trnasformations, you need to enter n lines. Another solution for this is to use Dummy Source (i.e. Source with one line) and you can pass loop parameters from that source and then use Java transfornmation and Java code to generate this sequence.
source to share
We have the ability to create a sequential number even if it is not available in the source.
-
Create a sequence generator transform. You will receive NEXTVAL and CURRVAL.
-
On the properties tab, you will have the opportunity to create sequence numbers.
- Initial values - the value from which it should start
- Increment by increment
- Ending value is the value it should end with.
- Current value - current value
- Cycle - if necessary, cyclical
- No cached values
- Reset
- Trace level
-
Connect NEXTVAL to the target column.
source to share