Informatica Common Components Source Qualifier external joins

You can use a source qualifier and an application source qualifier to convert an outer join that performs two sources in the same database. When PowerCenter executes an outer JOIN, it returns all rows from one of the source tables and the row in another source table that matches the join condition.

If you need to join two tables and return all rows from one of the tables, you can use an outer join. For example, you can perform an outer join when you want to join a registered Customer table and a monthly purchase list to determine if the purchase behavior of a registered customer is active. With an outer join, you can join the registered Customer table and the monthly purchase list and return all rows in the registered Customers table, including customers who did not purchase during the previous month. If you perform a normal join, PowerCenter will only return registered customers who have purchased during that month, as well as purchases made by registered customers only.

With an outer join, you can generate the same results as the primary outer join or the detail outer join in the join transformation. However, using an outer join reduces the number of rows in the data flow. This can improve performance.

PowerCenter supports two types of outer joins:

• Left outer JOIN. PowerCenter returns all rows in the table to the left of the join syntax and returns all rows in two tables that meet the join condition.

• Right outer join. PowerCenter returns all rows in the table to the right of the join syntax and returns all rows in two tables that meet the join condition.

Note: When overriding the default query, you can use nested query statements in outer joins.

Informatica Join syntax

When entering the join syntax, you can use the Informatica or database-specific join syntax. When using the Informatica join syntax, PowerCenter translates the syntax during the session and passes it to the source database.

Note: Database-specific syntax is always used for join conditions.

When using the Informatica join syntax, enclose the entire join statement in curly braces ({Informatica syntax}). When using database syntax, enter the syntax supported by the source database, not enclosed in curly braces.

When using the Informatica join syntax, the table name is used as the prefix for the column name. For example, if you have a column named First_Name in the Reg_customer table, enter Reg_customer in the join syntax. First_Name ". Also, when using aliases as table names, use the aliases within the Informatica join syntax to ensure that PowerCenter Server recognizes aliases.

The following table lists the join syntax for converting inputs for different source qualifiers at different locations when you create an outer join:

conversion	conversion Settings	description
Source Qualifier Conversion	A user-defined join	creates a join overlay. During a session, PowerCenter appends the join overlay to the WHERE clause of the default query.
	SQL query	/strong> Enter the join syntax directly in the where of the default query.
application Source Qualifier conversion	join overrides	to create a join overlay. During a session, PowerCenter appends the join overlay to the WHERE clause of the default query.
	extract overrides	strong> Enter the join syntax directly in the where of the default query.

You can combine left outer and right outer joins with normal joins in a single source qualifier. You can use multiple normal joins and multiple left outer joins.

When merging joins, enter the joins in the following order:

• Normal

• Left outer

• Right outside

Normal JOIN syntax

You can use join conditions in the source qualifier to create a normal join. However, when you create an outer join, you need to override the default join to perform an outer join. Therefore, you need to include a normal join in the join overlay. When you merge a normal join in a join overlay, the normal joins are listed first, and then the outer joins are listed. You can enter multiple normal joins in the join overlay.

To create a normal join, use the following syntax:

{ source1 INNER JOINsource2onjoin_condition }

The following table shows the syntax for a normal join in a join overlay:

syntax	description
source1	The source table name. PowerCenter will return rows from this table that match the join criteria.
source2	source table name. PowerCenter will return rows from this table that match the join criteria.
Join_ Condition	join condition. Use the syntax supported by the source database. You can combine multiple join conditions by using the AND operator.

For example, you have a reg_customer that contains registered customer data, a PURCHASES table that refreshes once a month

To return a row that displays the customer name for each transaction in June, use the following syntax:

{Reg_customer INNER JOIN PURCHASES on Reg_customer. cust_id = PURCHASES. CUST_ID}

PowerCenter will return a row with a matching customer ID. It does not include customers who did not purchase the act in June. It also excludes purchases made by non-registered customers.

Left OUTER JOIN syntax

You can use join overrides to create a left outer join. You can enter multiple left outer joins in a single join overlay. When using a left outer join with other joins, list all left outer joins after any normal joins in the statement.

To create a left outer join, use the following syntax: { source1 LEFT OUTER JOIN source2 on join_condition }

Grammar	Description
Source1	The name of the source table. For a left outer join, PowerCenter returns all rows in this table.
Source2	The name of the source table. PowerCenter will return rows from this table that match the join criteria.
Join_condition	The join condition. Use the syntax supported by the source database. You can combine multiple join conditions by using the AND operator.

For example, using the same reg_customer and PURCHASES tables described in the normal join syntax, you can use the following join overrides to determine how many customers have purchased in June:

{Reg_customer left OUTER joins PURCHASES on Reg_customer. cust_id = PURCHASES. CUST_ID}

PowerCenter Server returns all registered customers in the Reg_customers table and uses a null value for customers who do not have a purchase behavior in June. It does not include purchases made by non-registered customers.

You can use multiple join conditions to determine how many registered customers spend more than $100.00 in a single purchase in June:

{Reg_customer left OUTER joins PURCHASES on (Reg_customer. cust_id = PURCHASES. cust_id and PURCHASES. AMOUNT > 100.00)}

You can use multiple left outer joins if you need to merge information about the returns in the same period.

To determine how many customers have purchased and returned in June, you can use two left outer joins:

{Reg_customer left OUTER joins PURCHASES on Reg_customer. cust_id = PURCHASES. cust_id left OUTER joins RETURNS on Reg_customer. cust_id = PURCHASES. CUST_ID}

PowerCenter uses NULL for missing values.

Right outer join syntax

You can use join overrides to create a right outer join. If you reverse the table order in the join syntax, the right outer join returns the same results as the left outer join. Only a right outer join is used in the join overlay. If you want to create more than one right outer join, try reversing the order of the source tables and changing the join type to a left outer join.

When you use a right outer join with other joins, enter a right outer join at the end of the join overlay.

To create a right outer join, use the following syntax:

{ source1  right OUTER JOIN source2 on join_condition  } 

syntax	description
source1	The source table name. PowerCenter will return rows from this table that match the join criteria.
source2	source table name. For a right outer join, PowerCenter returns all rows in this table.
Join_ Condition	join condition. Use the syntax supported by the source database. You can combine multiple join conditions by using the AND operator.

You can use a right outer join with a left outer join to join and return all data in two tables, similar to a full outer join. For example, you can use the following join overrides to extract all registered customers and all purchases for June:

{Reg_customer left OUTER joins PURCHASES on Reg_customer. cust_id = PURCHASES. cust_id right OUTER joins PURCHASES on Reg_customer. cust_id = PURCHASES. CUST_ID}

Create an outer join

You can enter an outer join to overwrite as a join or as part of the override of the default query.

When you create a join overlay, Designer appends the join overlay to the WHERE clause of the default query. During the session, PowerCenter will transform the Informatica join syntax and include it in the default query for extracting source data. If possible, enter the join overlay instead of overriding the default query.

When overriding the default query, enter the join syntax in the WHERE clause of the default query. During the session, PowerCenter transforms the Informatica join syntax, and then uses the query to extract the source data. If you change the transform after the overwrite is created, PowerCenter ignores the change. Therefore, if possible, enter the outer join syntax as a join overlay.

To create an outer join as a join overlay:

• Open the Source qualifier transformation, and then click the Properties tab.

• In the Source qualifier transformation, click the button in the user-defined join field.

In the application Source qualifier transformation, click the button in the Join Override field.

• Enter the join syntax.

Do not enter where at the beginning of the join. PowerCenter Server is added when querying rows.

Enclose the Informatica join syntax in curly braces ({}).

Use aliases within the Informatica join syntax when using table aliases and the Informatica join syntax.

Use the table name as the column name prefix, such as table. Column.

Use the join conditions supported by the source database.

When you enter multiple joins, the joins are grouped by type, and are listed in the following order: normal, left, outer, right, and so on. Only one right outer join is included in each nested query.

Select the port name from the Ports tab to ensure accuracy.

• Click OK.

To create an outer join as an extraction overlay:

• After connecting the input and output ports of the application Source qualifier transformation, double-click the title bar of the transformation and select the Properties tab.

• In the application Source qualifier transformation, click the button in the Extract Overrides field.

• Click Generate SQL.

• In the WHERE clause, immediately after the where input join syntax.

Enclose the Informatica join syntax in curly braces ({}). Use aliases within the Informatica join syntax when using table aliases and the Informatica join syntax. Use the table name as the column name prefix, such as table. Column. Use the join conditions supported by the source database. When you enter multiple joins, the joins are grouped by type, and are listed in the following order: normal, left, outer, right, and so on. Only one right outer join is included in each nested query. Select the port name from the Ports tab to ensure accuracy.

• Click OK.

 

Informatica Common Component Source Qualifier external join