Table of Contents

Search

  1. Preface
  2. Components
  3. Business services
  4. File listeners
  5. Fixed-width file formats
  6. Hierarchical schemas
  7. Intelligent structure models
  8. Mapplets
  9. Saved queries
  10. Shared sequences
  11. User-defined functions

Components

Components

Saved query syntax

Saved query syntax

When you create a saved query, enter an SQL statement that is valid for the database that you want to run the query against.
You can use different SQL statements based on where you use the saved query:
Synchronization
task
When you create a saved query to use as the source in a synchronization task, the SQL statement must be a SELECT statement.
Data Integration
uses the SQL statement to retrieve source column information. You can edit the data type, precision, or scale of each column before you save the saved query.
For example, you might create a saved query based on a TRANSACTIONS table that includes transactions from 2016 with the following SQL statement:
SELECT TRANSACTION_ID, TRANSACTION_TOTAL, TRANSACTION_TIMESTAMP from dbo.TRANSACTIONS WHERE TRANSACTION_TIMESTAMP>’0:0:0:0 01/01/2016’
Data Integration
ensures that saved query column names are unique. If an SQL statement returns a duplicate column name,
Data Integration
adds a number to the duplicate column name as follows:
<column_name><number>
SQL transformation
When you create a saved query to use in an SQL transformation, you can use one or more of the following SQL statements in the query:

    ALTER

    CALL

    COMMENT

    COMMIT

    CREATE

    DELETE

    DROP

    EXPLAIN PLAN

    GRANT

    INSERT

    LOCK TABLE

    MERGE

    RENAME

    REVOKE

    ROLLBACK

    SELECT

    TRUNCATE

    UPDATE

Use the following guidelines when you create the query:
  • You can use aggregate functions such as COUNT with Salesforce connections only.
  • Do not use conversion functions, such as TO_CHAR or TO_DATE.
  • Do not use an asterisk (*) to select all columns of a table. List the columns that you want to select.
  • Do not use SQL transformation parameter binding or string substitution notation such as ?input_field? or ~input_field~ in the query. Saved queries have no information about SQL transformation input fields.
Test the SQL statement you want to use on the source database before you create a saved query.
Data Integration
does not display specific error messages for invalid SQL statements.


Updated August 03, 2020