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.
Tip: 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.