Entering a user-defined join is similar to entering a custom SQL query. However, you only enter the contents of the WHERE clause, not the entire query. When you perform an outer join, the Integration Service may insert the join syntax in the WHERE clause or the FROM clause of the query, depending on the database syntax.
When you add a user-defined join, the Source Qualifier transformation includes the setting in the default SQL query. However, if you modify the default query after adding a user-defined join, the Integration Service uses only the query defined in the SQL Query property of the Source Qualifier transformation.
You can use a parameter or variable as the user-defined join or include parameters and variables within the join. When including a string mapping parameter or variable, use a string identifier appropriate to the source system. For most databases, you need to enclose the name of a string parameter or variable in single quotes.
When you include a datetime parameter or variable, you might need to change the date format to match the format used by the source. The Integration Service converts a datetime parameter and variable to a string based on the source system.
To create a user-defined join:
Create a Source Qualifier transformation containing data from multiple sources or associated sources.
Open the Source Qualifier transformation, and click the Properties tab.
Click the Open button in the User Defined Join field.
The SQL Editor dialog box appears.
Enter the syntax for the join.
Do not enter the keyword WHERE at the beginning of the join. The Integration Service adds this keyword when it queries rows.
Enclose string mapping parameters and variables in string identifiers. Alter the date format for datetime mapping parameters and variables when necessary.
Click OK to return to the Edit Transformations dialog box, and then click OK to return to the Designer.
ASK INFAPreview
