Informatica like operator

Question

Need help in understanding logic. I have 2 tables from different database. There is one field in common but not the data completely.

As an example: table 1 the field data is coming as :test

 table 2 its coming as - select test from tablename

I need to compare and fetch all the data from table 2 where its matching with data in table 1 i.e test .I am trying to use full outer join and then regular match to fetch the records test but its not working. Can someone pls help in understanding the ideal solution for this problem

Answer 1

You can use a SQL transformation or you can use the method you are using full join and instr().

A. Using SQL transformation -

Use table1 as Source qualifier. Pass the column you want to match (lets assume the col name is match_col).

1. Use an expressoion to add % to match_col.
2. Use passive SQL transformation with static SQL. SQL would look like

SELECT t2.col1, t2.col2 FROM table2 t2 WHERE t2.match_col like ?match_col?

3. capture all columns from table1 and new columns from table2 and pass them to the target. Mapping will be like

SQ --> EXP --> SQL_Transform -> Tgt

B. Using full join and instr() - This is an in-effieicnt method but it will work when the data volume is low.

1. Use joiner to join table1 and table 2. do a full outer join on dummy columns.
2. Use an expression transformation. Create a port like this

match_nomatch = iff( instr(upper(table1_match_col), upper(table2_match_col))>0, 'Match','No Match')

3. Add a filter with condition - match_nomatch ='Match'

Mapping will be like

SQ --> |
SQ_2--> JNR -> EXP--> FIL-->Tgt