Source Qualifier Transformation

Tasks performed

1. Join data Originating from same source database.
2. Filter records when informatica reads source data
3. Specify an outer join rather than the default inner join.
4. Specify sorted ports
5. Select only distinct values from the source
6. Create custom query to issue special SELECT statement for informatica server to read data.

Data types

The transformation data types in the Source qualifier determine how the source database binds data when the informatica server reads it. Mapping is invalidated while saving, if the data types in the source definition and source qualifier do not match.

Target Load Order

If you have multiple source Qualifiers connected to multiple targets, you can designate the order in which the informatica server has to load data into the targets.

 If one Source Qualifier provides data for multiple targets, you can enable constraint-based loading in a session to have informatica load data based on target table primary and foreign key relationships.

Parameters and Variables

• Mapping Parameters and Variables can be used in SQL query, user defined join, source filter
• System Variable $$$SessStartTime can also be used.
• Use database specific functions to convert the default date format.

Automatic Format Conversion for Datetime Mapping Parameters and Variables
Source	Date Format
DB2	YYYY-MM-DD-HH24:MI:SS
Informix	YYYY-MM-DD HH24:MI:SS
Microsoft SQL Server	MM/DD/YYYY HH24:MI:SS
ODBC	YYYY-MM-DD HH24:MI:SS
Oracle	MM/DD/YYYY HH24:MI:SS
Sybase	MM/DD/YYYY HH24:MI:SS

Default Query

For relational sources, the Informatica Server generates a query for each Source Qualifier when it runs a session. The Informatica Server reads only the columns in Source Qualifier that are connected to another transformation.

Source Definition connected to Source Qualifier Transformation

 Although there are many columns in the source definition, only three columns are connected to another transformation. In this case, the Informatica Server generates a default query that selects only those three columns:

SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.COMPANY, CUSTOMERS.FIRST_NAME
FROM CUSTOMERS

When generating the default query, the Designer delimits table and field names containing the slash character (/) with double quotes.

Overriding the Default Query

You can alter or override the default query in the Source Qualifier by changing the default settings of the transformation properties. When you edit transformation properties, the Source Qualifier includes these settings in the default query. However, if you enter an SQL query, the Informatica Server uses only the defined SQL statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation.

In the SQL override query, do not change the list of selected ports or the order in which they appear in the query. This list must exactly match the connected transformation output ports

Joining Source Data

When a mapping uses related relational sources, you can join both sources in one Source Qualifier transformation. During the session, the source database performs the join before passing data to the Informatica Server. This can increase performance when source tables are indexed.

Default Join

When you join related tables in one Source Qualifier, the Informatica Server joins the tables based on the related keys in each table.

This default join is an inner equijoin, using the following syntax in the WHERE clause:

Source1.column_name = Source2.column_name

The columns in the default join must have:

• A primary-foreign key relationship
• Matching datatypes

Custom joins

If you need to override the default join, you can enter just the contents of the WHERE clause that specifies the join in the custom query.

You might need to override the default join under the following circumstances:

• Columns do not have a primary-foreign key relationship.
• The datatypes of columns used for the join do not match.
• You want to specify a different type of join, such as an outer join.

Heterogeneous Joins

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

• Join data from different source databases
• Join data from different flat file systems
• Join relational sources and flat files

Creating Key Relationships

You can only join tables in the Source Qualifier if the tables have primary-foreign key relationships. However, you can create primary-foreign key relationships in the Source Analyzer by linking matching columns in different tables. These columns do not have to be keys, but they should be included in the index for each table. These primary-foreign key relationships exist in the metadata only. You do not need to generate SQL or alter the source tables.

Outer Join Support

The Informatica Server supports two kinds of outer joins:

• Left. Informatica Server returns all rows for the table to the left of the join syntax and the rows from both tables that meet the join condition.
• Right. Informatica Server returns all rows for the table to the right of the join syntax and the rows from both tables that meet the join condition.

Informatica Join Syntax

When you enter join syntax, you can use Informatica join syntax instead of database-specific join syntax. When you use the Informatica join syntax, the Informatica Server translates the syntax and passes it to the source database during the session. If desired, you can use database-specific join syntax.

When you use Informatica join syntax, enclose the entire join statement in braces ({Informatica syntax}). When you use database syntax, enter syntax supported by the source database without braces.

When using Informatica join syntax, use table names to prefix column names. For example, if you have a column named FIRST_NAME in the REG_CUSTOMER table, enter “REG_CUSTOMER.FIRST_NAME” in the join syntax. Also, when using an alias for a table name, use the alias within the Informatica join syntax to ensure the Informatica Server recognizes the alias.

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. However due to limitations with some databases, you can only use one right outer join in a source qualifier.

When you combine joins, enter them in the following order:

1. Normal

2. Left outer

3. Right outer

Normal Join Syntax

{ source1 INNER JOIN source2 on join_condition }

Join_condition- Use syntax supported by the source database. You can combine multiple join conditions with the AND operator.

Example:  { REG_CUSTOMER INNER JOIN PURCHASES on    REG_CUSTOMER.CUST_ID = PURCHASES.CUST_ID and  PURCHASES.CUST_ID >1000}

Left Outer Join Syntax

{ source1 LEFT OUTER JOIN source2 on join_condition }

You might use multiple left outer joins

Example: { REG_CUSTOMER LEFT OUTER JOIN PURCHASES on REG_CUSTOMER.CUST_ID = PURCHASES.CUST_ID LEFT OUTER JOIN RETURNS on REG_CUSTOMER.CUST_ID = PURCHASES.CUST_ID }

Right Outer Join Syntax

{ source1 RIGHT OUTER JOIN source2 on join_condition }

Example(Full Outer join) :{REG_CUSTOMER LEFT OUTER JOIN PURCHASES on REG_CUSTOMER.CUST_ID = PURCHASES.CUST_ID RIGHT OUTER JOIN PURCHASES on REG_CUSTOMER.CUST_ID = PURCHASES.CUST_ID }

Creating an Outer Join

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

When you create a join override in a Source Qualifier transformation, the Designer appends the join override to the WHERE clause of the default query. During the session, the Informatica Server translates the Informatica join syntax and includes it in the default query used to extract source data. When possible, enter a join override instead of overriding the default query.

When you override the default query, enter the join syntax in the WHERE clause of the default query. During the session, the Informatica Server translates Informatica join syntax and then uses the query to extract source data. If you make changes to the transformation after creating the override, the Informatica Server ignores the changes. Therefore, when possible, enter outer join syntax as a join override.

To create an outer join as a join override:

1. Open the Source Qualifier transformation, and click the Properties tab.

2. In a Source Qualifier transformation, click the button in the User Defined Join field.

In an Application Source Qualifier, click the button in the Join Override field.

3. Enter the syntax for the join.

Do not enter WHERE at the beginning of the join. The Informatica Server adds this when querying records.

Enclose Informatica join syntax in braces ( { } ).

When using an alias for a table as well as the Informatica join syntax, use the alias within the Informatica join syntax.

Use table names to prefix columns names, for example, “table.column”.

Use join conditions supported by the source database.

When entering multiple joins, group joins together by type, and then list them in the following order: normal, left outer, right outer. Include only one right outer join per nested query.

Select port names from the Ports tab to ensure accuracy.

4. Click OK to return to the Edit Transformations dialog box, and then click OK to return to the Designer.

5. Choose Repository-Save.

To create an outer join as an extract override:

1. After connecting the input and output ports for the Application Source Qualifier transformation, double-click the title bar of the transformation and select the Properties tab.

2. In an Application Source Qualifier, click the button in the Extract Override field.

3. Click Generate SQL.

4. Enter the syntax for the join in the WHERE clause immediately after the WHERE.

Enclose Informatica join syntax in braces ( { } ).

When using an alias for a table as well as the Informatica join syntax, use the alias within the Informatica join syntax.

Use table names to prefix columns names, for example, “table.column”.

Use join conditions supported by the source database.

When entering multiple joins, group joins together by type, and then list them in the following order: normal, left outer, right outer. Include only one right outer join per nested query.

Select port names from the Ports tab to ensure accuracy.

5. Click OK to return to the Edit Transformations dialog box, and then click OK to return to the Designer.

6. Choose Repository-Save.

Common Database Syntax Restrictions

Different databases have different restrictions on outer join syntax. Consider the following restrictions when you create outer joins:

• Do not combine join conditions with the OR operator in the ON clause of outer join syntax.
• Do not use the IN operator to compare columns in the ON clause of outer join syntax.
• Do not compare a column to a subquery in the ON clause of outer join syntax.
• When combining two or more outer joins, do not use the same table as the inner table of more than one outer join. For example, do not use either of the following outer joins:

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA TABLE3 LEFT OUTER JOIN TABLE2 ON TABLE3.COLUMNB = TABLE2.COLUMNB }

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA TABLE2 RIGHT OUTER JOIN TABLE3 ON TABLE2.COLUMNB = TABLE3.COLUMNB}

• Do not use both tables of an outer join in a regular join condition. For example, do not use the following join condition:

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA WHERE TABLE1.COLUMNB = TABLE2.COLUMNC}

However, you can use both tables in a filter condition, like the following:

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA WHERE TABLE1.COLUMNB = 32 AND TABLE2.COLUMNC > 0}

Note: Entering a condition in the ON clause might return different results from entering the same condition in the WHERE clause.

• When using an alias for a table, use the alias to prefix columns in the table. For example, if you call the REG_CUSTOMER table C, when referencing the column FIRST_NAME, use “C.FIRST_NAME”.

Adding Pre- and Post-Session SQL Commands

You can add pre- and post-session SQL commands on the Properties tab in the Source Qualifier transformation. The Informatica Server executes pre-session SQL commands against the source database before it reads the source. It executes post-session SQL commands against the source database after it writes to the target.

Use the following guidelines when you enter pre- and post-session SQL commands in the Source Qualifier transformation:

• You can use any command that is valid for the database type. However, the Informatica Server does not allow nested comments, even though the database might.
• You can use mapping parameters and variables in the source pre- and post-session SQL commands.
• Use a semi-colon (;) to separate multiple statements.
• The Informatica Server ignores semi-colons within single quotes, double quotes, or within /* ...*/.
• If you need to use a semi-colon outside of quotes or comments, you can escape it with a back slash (\). When you escape the semi-colon, the Informatica Server ignores the backslash, and it does not use the semi-colon as a statement separator.
• The Designer does not validate the SQL.