top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Join multiple tables located in different databases using Informatica

+1 vote
1,692 views

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?

posted Mar 10, 2014 by Rohini Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

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.

answer Mar 10, 2014 by Shweta Singh
0 votes

If all your Netezza tables belongs to same schema then import all tables structure into the mapping, keep one source qualifier and through SQL Override join all the tables.
Then Join the output of Netezza Source Qualifier with SQL Server Table's source qualifier through Joiner Transformation.

But, if all your tables belongs to different-2 schema then you can use 5 joiner transformation to join all 6 tables.

answer Apr 25, 2014 by Shatark Bajpai
Similar Questions
+2 votes

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.

+1 vote

In my source table data is

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc 

Table s2

p_name,value 
p1,    20
p2,    xyz
p3,    abc 
0 votes

I have some files that I would like to consolidate into a single database table. The files have similar but different formats. The files look something like this:

FileOne:
•ColA : string
•ColB : string
•ColC : string

FileTwo:
•ColAA : string
•ColBB : string
•ColCC : string

FileThree:
•Col01 : string
•Col02 : string
•Col03 : string

The destination table looks like this:

TableDestination:
•ColFirst : string
•ColSecond : string
•ColThird : string

I want to develop a mapping that ETLs these three files into this one database, but because the column names are different, it looks like I'll have to develop three different mappings, or three different sources, or three different somethings. The problem is that my example is contrived: I actually have many different files that all have different formats and column names, but the data is all very similar.

I would like to develop a single mapping or workflow that can handle all of this by only adding a table that holds the column mappings. Such a table would look like this based on the sample files and sample table above:

TableMappings:
enter image description here

In this way, to edit a column mapping I only have to make an edit this this TableMappings table. I wouldn't have to make any changes at all to the mapping or workflow. Nor would I have to redeploy an application.

What would a mapping or workflow look like that could take advantage of something like this? I assume there'd be a flat file source that takes files from a folder. There would be something in the middle that uses this TableMappings table to map column names. Finally there would be a relational data object that represents my destination database table "TableDestination". I don't know how to put this together though.

...