Informatica Common Component Source qualifier join query

Join source data

You can use a Source qualifier transformation to join data from multiple relational tables. These tables must be accessible from the same instance or database server.
When mapping uses related relational sources, you can join two sources simultaneously in a source qualifier transformation. During a session, the source database performs a join before passing data to PowerCenter. This operation can enhance performance if the source table is indexed.
Tip: Use a join transformation for heterogeneous sources and join a flat file with a join transformation.

Default join when you join a related table in a source qualifier transformation, PowerCenter joins the table based on the key in each table. This default join is an inner-equal join, using the following syntax in the WHERE clause: Source1.column_name = source2.column_name The columns in the default join must have: PRIMARY key-foreign key relationship matching data type

For example, you can view all orders for the current month, including the order number, order quantity, and customer name. The Orders table includes the order number and the quantity of each order, but does not include the customer name. To include the customer name, you need to join the ORDERS and CUSTOMERS tables. Because two tables include the customer ID, you can join two in a source qualifier transformation

Table.

When you include more than one table, PowerCenter Server generates a SELECT statement for all columns used in the map. In this case, the SELECT statement is similar to the following statement:

SELECT        CUSTOMERS. customer_id,       Customers.company,      CUSTOMERS. First_Name,       CUSTOMERS. last_name,      CUSTOMERS. ADDRESS1,       CUSTOMERS. ADDRESS2,       CUSTOMERS. City,      CUSTOMERS. State,       CUSTOMERS. Postal_Code,       CUSTOMERS. PHONE,       CUSTOMERS. EMAIL,      ORDERS. order_id,      ORDERS. date_entered,       ORDERS. date_promised,       ORDERS. date_shipped,       ORDERS. employee_id,       ORDERS. customer_id,       ORDERS. Sales_tax_rate,       ORDERS. store_id       fromWHERE CUSTOMERS. customer_id=ORDERS. customer_id

View Code

The WHERE clause is an equal join, which includes customer_id from the ORDERS and CUSTOMER tables

Custom Join if you need to override the default join, you can enter the contents of the WHERE clause, which specifies the joins in the custom query. You may need to override the default join in the following situations:

1. The column does not have a primary key-foreign key relationship.
2. The data type of the column used by the join does not match.
3. You need to specify a different type of join, such as an outer join. Heterogeneous joins

To perform a heterogeneous join, use a join transformation. Use the Join transformation when you need to join the following types of sources:

1. Joining data from different source databases
2. Joining data from different flat file systems
3. Join relationship sources and flat files

Create a key relationship

If the table has a primary key-foreign key relationship, you can join the table in the Source qualifier transformation. However, you can create a primary key-foreign key relationship in Source Analyzer by linking the matching columns in different tables. These columns do not have to be keys, but they should be included in the index of each table.

Tip: If the source table has more than 1000 rows, you can enhance performance by indexing the primary key-foreign key. Indexing a primary key-foreign key can degrade performance if the source table is less than 1000 rows.
For example, a retail chain office needs to receive payments based on an order extraction. ORDERS and PAYMENTS tables do not share primary and foreign keys. However, two tables include the date_shipped column. You can create a primary key-foreign key relationship in the metadata of Source Analyzer. Note that two tables are not linked. Therefore, the Designer does not recognize the relationship on the date_shipped column.
You can create a relationship between ORDERS and PAYMENTS tables by linking the date_shipped column. Designer will automatically add primary and foreign keys to the date_shipped column in the ORDERS and PAYMENTS table definitions.

Create a relationship between two tables if the column is not connected, the Designer will not recognize the relationship. Primary key-foreign key relationships exist only in metadata. You do not need to generate SQL or change the source table. Once a key relationship exists, you can use the Source qualifier transformation to join two tables. By default, joins are based on date_shipped.

Informatica Common Component Source qualifier join query