Praveen Vasudev

• Sep 12th, 2005

 

Normal (Default) -- only matching rows from both master and detail
Master outer -- all detail rows and only matching rows from master
Detail outer -- all master rows and only matching rows from detail
Full outer  -- all rows from both master and detail ( matching or non matching)

sithusithu

 Profile Answers by sithusithu 

• Jan 19th, 2006

 

follw this

1. In the Mapping Designer, choose Transformation-Create. Select the Joiner transformation. Enter a name, click OK.

The naming convention for Joiner transformations is JNR_TransformationName. Enter a description for the transformation. This description appears in the Repository Manager, making it easier for you or others to understand or remember what the transformation does.

The Designer creates the Joiner transformation. Keep in mind that you cannot use a Sequence Generator or Update Strategy transformation as a source to a Joiner transformation.

2. Drag all the desired input/output ports from the first source into the Joiner transformation.

The Designer creates input/output ports for the source fields in the Joiner as detail fields by default. You can edit this property later.

3. Select and drag all the desired input/output ports from the second source into the Joiner transformation.

The Designer configures the second set of source fields and master fields by default.

4. Double-click the title bar of the Joiner transformation to open the Edit Transformations dialog box.

5. Select the Ports tab.

6. Click any box in the M column to switch the master/detail relationship for the sources. Change the master/detail relationship if necessary by selecting the master source in the M column.

Tip: Designating the source with fewer unique records as master increases performance during a join.

7. Add default values for specific ports as necessary.

Certain ports are likely to contain NULL values, since the fields in one of the sources may be empty. You can specify a default value if the target database does not handle NULLs.

8. Select the Condition tab and set the condition.

9. Click the Add button to add a condition. You can add multiple conditions. The master and detail ports must have matching datatypes. The Joiner transformation only supports equivalent (=) joins:

 

10. Select the Properties tab and enter any additional settings for the transformations.

11. Click OK.

12. Choose Repository-Save to save changes to the mapping.

Cheers,

Sithu

audi

• Oct 8th, 2012

 

There are 4 Types of Joiner Trasnformations:
1) Normal
2) Master Outer
3) Detail Outer
4) Full Outer

Note: A normal or master outer join performs faster than a full outer or detail outer join.

Example: In EMP, we have employees with DEPTNO 10, 20, 30 and 50. In DEPT, we have DEPTNO 10, 20, 30 and 40. DEPT will be MASTER table as it has less rows.

Normal Join: With a normal join, the Power Center Server discards all rows of data from the master and detail source that do not match, based on the condition.
All employees of 10, 20 and 30 will be there as only they are matching.

Master Outer Join: This join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
• All data of employees of 10, 20 and 30 will be there.
• There will be employees of DEPTNO 50 and corresponding DNAME and LOC Columns will be NULL.

Detail Outer Join: This join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
• All employees of 10, 20 and 30 will be there.
• There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Full Outer Join: A full outer join keeps all rows of data from both the master and detail sources.
• All data of employees of 10, 20 and 30 will be there.
• There will be employees of DEPTNO 50 and corresponding DNAME and LOC Columns will be NULL.
• There will be one record for DEPTNO 40 and corresponding data of EMP Columns will be NULL.

Kiran Ghatage

• Nov 15th, 2014

 

Look into following mappings
https://community.informatica.com/solutions/developer_client_full_outer_join
https://community.informatica.com/solutions/developer_client_self_join_using_joiner

ravi ranjan

• Jul 22nd, 2016

 

Joiner transformation having four types
1 Normal join
2 Master outer join
3 Detail outer join
4 Full join