How to create informatica mapping from sql query

Question

I am new to informatica development I want to create mapping where I need to check if any duplicates. I have below sql query from which I have to create the mapping.

Select count(1), A.market_cd from (select distinct account_no, market_cd from Db1.Table1 where $$monthenddate between date(eff_begin) and date(eff_end) group by account_no, market_cd having count(1) > 1 ) A group by market_cd ;

Select count(1), A.market_cd from (select distinct account_no, market_cd from Db2.Table2 where $$monthenddate between date(eff_begin) and date(eff_end) group by account_no, market_cd having count(1) > 1 ) A group by market_cd ;

Select count(1), A.market_cd from (select distinct account_no, market_cd from Db3.Table3 where $$monthenddate between date(eff_begin) and date(eff_end) group by account_no, market_cd having count(1) > 1 ) A group by market_cd

Result:-

Count | Market_cd

0

Ideal result for the above queries should be zero rows. I have to check if the result gives any count or not.

I have to Union all the above queries in target flat file. What logic and transformation can be used to create the mapping? Please can someone help

Answer 1

I am with Maciejg, please try for your own and post if you face any issues. But here is initial guidance to start off.
Since you are reading from a table, you can use your sql directly on them and get the result and UNION them.
If you are looking for purely infa mapping then this is how we count/check duplicates -
1. SQ - read all data from Table1 -> Flter $$mapping_param >=eff_begin and $$mapping_param <=eff_end -> Sorter (Sort by market_id) ->aggregator (group by market_id, add one outputport cnt = count(*)) -> Joiner to join data from sorter and aggregator on market_id ->Filter cnt >1
2. Follow step1 for table2 and table3.
3. Union all above steps.
4. put a flat file target.