1. What is a union transformation?
A union transformation is used merge data from multiple sources similar to the UNION ALL SQL statement to combine the results from two or more SQL statements.
2. As union transformation gives UNION ALL output, how you will get the UNION output?
Way 1:-
Pass the output of union transformation to a sorter transformation. In the properties of sorter transformation check the option select distinct.
way 2:-
we can pass the output of union transformation to aggregator transformation and in the aggregator transformation specify all ports as group by ports.
3. What are the guidelines to be followed while using union transformation?
The following rules and guidelines need to be taken care while working with union transformation:
Union is an active transformation because it combines two or more data streams into one. Though the total number of rows passing into the Union is the same as the total number of rows passing out of it, and the sequence of rows from any given input stream is preserved in the output, the positions of the rows are not preserved, i.e. row number 1 from input stream 1 might not be row number 1 in the output stream. Union does not even guarantee that the output is repeatable.
Regards,
Hari Babu
A union transformation is used merge data from multiple sources similar to the UNION ALL SQL statement to combine the results from two or more SQL statements.
Way 1:-
Pass the output of union transformation to a sorter transformation. In the properties of sorter transformation check the option select distinct.
way 2:-
we can pass the output of union transformation to aggregator transformation and in the aggregator transformation specify all ports as group by ports.
The following rules and guidelines need to be taken care while working with union transformation:
- You can create multiple input groups, but only one output group.
- All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.
- The Union transformation does not remove duplicate rows.
- You cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.
Union is an active transformation because it combines two or more data streams into one. Though the total number of rows passing into the Union is the same as the total number of rows passing out of it, and the sequence of rows from any given input stream is preserved in the output, the positions of the rows are not preserved, i.e. row number 1 from input stream 1 might not be row number 1 in the output stream. Union does not even guarantee that the output is repeatable.
Regards,
Hari Babu
Well,this is a very knowledgable and insightful blog post about Informatica and its relatable aspects.
ReplyDeleteInformatica Read Soap API