Search code examples
sqlplsqletlinformaticainformatica-powercenter

Sequence generation based on a conditon using Informatica

I need to achieve following data transformation using Informatica,

The first picture is the sample input data

enter image description here

The data after transformations should look like as below, Here for the different type I have the sequence staring from 200 in this case bfd should be considered as one group and (klm,kln) together as other group. The new id is id+the sequence number

enter image description here

Should I do this using UDF or by creating a procedure.. or using some sets of transformations.. I am new to informatica and am confused about what approach should I follow.. Thanks for the help in advance!

Solution

  • you can do this using two separate sequence transformation.
    first one will start from 0 and increment by 1.
    second one will start from 200 and increment by 1.
    Then use an IIF condition to generate new_id column. if you have few different type of sequence, you can do it with IIF. But if you have hundreds, probably you need to use some UDF etc.

    seq_1 = attach NEXTVAL from sequence generator 1
seq_2 = attach NEXTVAL from sequence generator 2
new_id = TO_INTEGER ( IIF( group ='bfd', id || seq_1, 
                        IIF( group = 'klm' or group ='kln', id || seq_2) 
                       )  
                     )