informaticainformatica-powercenterinformatica-cloud

I have to convert below SQL Query to informatica cloud logic

Out put of joiner transformation(TestData table name in SQL Query) as below

enter image description here

I need to load data in to target as below

enter image description here

I wrote SQL Query like

SELECT * FROM TestData AS A WHERE SourceSystem = 'NC' AND EXISTS (SELECT * FROM TestData AS B WHERE B.ClusterID = A.ClusterID AND B.SourceString = '50012559' AND B.SourceSystem = 'ACB' )

can you help me how to covert this SQL Query to Informatica cloud .

Solution

  • Assuming testData is a joiner output here is the solution. I assumed, cluster id is the unique key. if it not unique key, then this solution will cause duplicates.

    First, sort your joiner output TestData by ClusterID.

    Then put a filter(filter1) on SourceSystem = 'NC' and create pipeline 1. Connect another filter(filter2) to sorter on SourceString = '50012559' AND B.SourceSystem = 'ACB' and create pipeline 2. Add another joiner - conditions will be pipeline1.ClusterID =pipeline2.ClusterID.

    The output from joiner will be your desired data.

    This is how the mapping would look like -

                                        |-Filter 1 ->|  
TestData_Joiner... -SRT_ClusterID-->|-Filter 2 ->| -JNR_ClusterID-> <desired output>

    Pls note, this will generate data just like below SQL. `` SELECT * FROM TestData AS A WHERE SourceSystem = 'NC' AND EXISTS (SELECT * FROM TestData AS B WHERE B.ClusterID = A.ClusterID AND B.SourceString = '50012559' AND B.SourceSystem = 'ACB' )