Informatica Common Components Source qualifier SQL Query

The Source qualifier transformation provides SQL query options to overwrite the default query. You can enter the SQL statements that your source database supports. Before you enter the query, connect all the input and output ports that you want to use in the map.

When you edit a SQL query, you can build and edit the default query. When the Designer generates a default query, it merges all other configured options, such as the filter or the number of sorted ports. The resulting query overrides any other options that you might subsequently configure in the transformation.

You can include mapping parameters and variables in your SQL query. When you include a string mapping parameter or variable, use the string identifier that applies to the source system. For most databases, you apply single quotation marks around string parameters or variable names.

When you include datetime parameters or variables, you may want to change the date format to match the format used by the source. PowerCenter will convert datetime parameters and variables to strings based on the source system.

When you create a custom SQL query, the SELECT statement must list the port names in the order in which they appear in the transformation.

If you edit a SQL query, you must enclose all the database reserved words in quotation marks.

To override the default query:

1. Open the source qualifier conversion, and then click the Properties tab.

2. Click the Open button in the SQL query field. The SQL Editor dialog box is displayed.

3. Click "Generate SQL".

When all source query rows are included from the Source qualifier transformation, the Designer displays the default query it generates.

4. Enter your own query where the default query appears.

Each column name must be qualified by the name of the table, view, or synonym in which it resides. For example, if you want to include the order_id column from the Orders table, enter orders. order_id. You can double-click the column name that appears in the ports window to avoid typing the name of each column. Enclose string mapping parameters and variables with a string identifier. If necessary, change the date time map parameter and the date format of the variable.

5. Select the ODBC data source that contains the sources included in the query.

6. Enter the user name and password to connect to this database.

7. Click "Validate". Designer runs the query and reports that the syntax is correct.

8. Click OK to return to the Edit Transform dialog box. Click OK again to return to the Designer.

9. Select "Database-save".

Tip: You can adjust the size of the expression editor. Drag the border of the dialog box to enlarge it. Designer Saves the new dimensions of the dialog box as customer settings.

Informatica Common Components Source qualifier SQL Query