Join multiple tables located in different databases using Informatica [CLOSED]

Question

I am trying to join 6 tables where 5 tables are located in Netezza database and the last table is present in SQL Server database. I need to join these six tables and extract all the required fields and load them into the target table which is located on Netezza.I tried writing SQL in SQL override to join all the tables but it throws me an error. Can someone help me how to resolve this issue?

Thank you.

Answer 1

You need to use Joiner transformation.

First join the 5 tables which are located in the same database. Put this SQL that joins 5 tables in one source qualifier. This will join and extract the data from one database. Next create another source qualifier to extract data from the 6th table.

Finally join the data from these two source qualifiers using a Joiner Transformation and you are done.

You have 6 tables in source and you want one table in your output. Problem is, you can't join 6 tables together in a single source qualifier as they belong to separate databases. In fact, you can not even join the 5 tables in the same database together as joining needs to happen via the 6th table. I am suggesting you to extract all these 6 tables individually into a single database (e.g. staging area) using one Informatica mapping (having 6 pipelines) and then create another mapping that will join these 6 staging tables together in one SQ, and load them to the target