Union

[kpmINFO]

Is there a function like union in oracle within Informatica, if not what could be the best way to achive this.

Example
I have two diff transformations with data
Transformation 1

id Val
1 a
2 b
3 c

Transformation 2
id Val
4 d
5 e

I would like to combine the data from these transformations and generate another transformation with
data
Id Val
1 a
2 b
3 c
4 d
5 e

Can this be achived in informatica.

 

[Sridharan]

Simplest way override the SQL...

Sri

 

[blom0344]

I believe version 7 will have a union transformation available, other than override,you could load to target and use primary key to bounce duplicates (which would otherwise be handled by the UNION action).....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[kpmINFO]

Sri
Thank you for ur reply.
I am using 6.2 and can you plaese give me more details as to how I can override the SQL. If possible can you gave me the query.

 

[Sridharan]

Blom is right Ver 7.x has Union Transformation... Now in your case what you need to do is Override the SQL of the Source Qualifier with a Union statement to get the functionality you are looking out for...

Sri

 

[anant13703]

What you can do is (as suggested by SRI)...generate the SQL in the SQL Qualifier...which would be..

SELECT ID, VAL FROM TABLE1

NOW ...put a union clause there and just change the "FROM" table. So the query will become..

SELECT ID, VAL FROM TABLE1
UNION ALL
SELECT ID, VAL FROM TABLE2

Now , VISUALLY, the ports coming into the SQL Qualifier and the ones going out of the SQL qualifier will be of the TABLE1 only. Infact you need not pull the TABLE 2 in the mapping. BUT, in actuality, the FETCH is done from both the tables..TABLE1 and TABLE2 .

note: The fields coming out from both the tables should be exactly same in data type and precesion and scale.

i hope it help

Anant

 

[kc1979]

hi kpmINFO,

i know a way to do union in Informatica. I have documented it in a word document with screenshots. If u give me ur email i can send it 2 u.

regards,
Karthik