Table of Contents

Search

  1. Preface
  2. Working with Transformations
  3. Aggregator Transformation
  4. Custom Transformation
  5. Custom Transformation Functions
  6. Data Masking Transformation
  7. Data Masking Examples
  8. Expression Transformation
  9. External Procedure Transformation
  10. Filter Transformation
  11. HTTP Transformation
  12. Identity Resolution Transformation
  13. Java Transformation
  14. Java Transformation API Reference
  15. Java Expressions
  16. Java Transformation Example
  17. Joiner Transformation
  18. Lookup Transformation
  19. Lookup Caches
  20. Dynamic Lookup Cache
  21. Normalizer Transformation
  22. Rank Transformation
  23. Router Transformation
  24. Sequence Generator Transformation
  25. Sorter Transformation
  26. Source Qualifier Transformation
  27. SQL Transformation
  28. Using the SQL Transformation in a Mapping
  29. Stored Procedure Transformation
  30. Transaction Control Transformation
  31. Union Transformation
  32. Unstructured Data Transformation
  33. Update Strategy Transformation
  34. XML Transformations

Transformation Guide

Transformation Guide

Adding an SQL Query

Adding an SQL Query

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.
  1. Open the Source Qualifier transformation, and click the Properties tab.
  2. Click the Open button in the SQL Query field.
    The SQL Editor dialog box appears.
  3. Click Generate SQL.
    The Designer displays the default query it generates when querying rows from all sources included in the Source Qualifier transformation.
  4. 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.
  5. Select the ODBC data source containing the sources included in the query.
  6. 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.
  7. Click Validate.
    The Designer runs the query and reports whether its syntax was correct.
  8. 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.

0 COMMENTS

We’d like to hear from you!