The Source Qualifier transformation provides the SQL Query option to override the default query. You can enter an SQL statement supported by the source database. Before entering the query, connect all the input and output ports you want to use in the mapping.
When you edit the SQL Query, you can generate and edit the default query. When the Designer generates the default query, it incorporates all other configured options, such as a filter or number of sorted ports. The resulting query overrides all other options you might subsequently configure in the transformation.
You can use a parameter or variable as the SQL query or include parameters and variables within the query. 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 value or a datetime mapping parameter or variable in the SQL query, change the date format to match the format used by the source. The Integration Service converts a datetime value to a string based on the source system.
Use the following rules and guidelines when you enter a custom SQL query:
The SELECT statement must list the port names in the order in which they appear in the transformation.
If the source is Microsoft SQL Server, the number of columns in the SELECT statement in the query must match the number of ports in the Source Qualifier transformation. Otherwise, the session might fail with the following error:
SQL Error [FnName: Fetch Optimize -- [Informatica][ODBC SQL Server Wire Protocol driver] Number of bound columns exceeds the number of result columns.].
When you override the default SQL query for a session configured for pushdown optimization, the Integration Service creates a view to represent the SQL override. It then runs an SQL query against this view to push the transformation logic to the database.
If you edit the SQL query, you must enclose all database reserved words in quotes.
Open the Source Qualifier transformation, and click the Properties tab.
Click the Open button in the SQL Query field.
The SQL Editor dialog box appears.
Click Generate SQL.
The Designer displays the default query it generates when querying rows from all sources included in the Source Qualifier transformation.
Enter a query in the space where the default query appears.
Every column name must be qualified by the name of the table, view, or synonym in which it appears. For example, if you want to include the ORDER_ID column from the ORDERS table, enter ORDERS.ORDER_ID. You can double-click column names appearing in the Ports window to avoid typing the name of every column.
You can use a parameter or variable as the query, or you can include parameters and variables in the query.
Enclose string mapping parameters and variables in string identifiers. Alter the date format for datetime mapping parameters and variables when necessary.
Select the ODBC data source containing the sources included in the query.
Enter the user name and password to connect to this database.
The Use Kerberos Authentication option indicates that the database in the connection runs on a network that uses Kerberos authentication. If this option is selected, you cannot enter the user name and password. The connection uses the credentials of the user account logged in to the machine where the Designer runs.
Click Validate.
The Designer runs the query and reports whether its syntax was correct.
Click OK to return to the Edit Transformations dialog box. Click OK again to return to the Designer.
Tip:
You can resize the Expression Editor. Expand the dialog box by dragging from the borders. The Designer saves the new size for the dialog box as a client setting.
ASK INFAPreview
