Saved Query Syntax
When you create a saved query, enter an SQL SELECT statement to select the source columns you want to use. Use an SQL statement that is valid for the source database.
Informatica Cloud uses the SQL statement to retrieve source column information. You can edit the datatype, 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’
Informatica Cloud ensures that saved query column names are unique. If an SQL statement returns a duplicate column name, Informatica Cloud adds a number to the duplicate column name as follows:
<column_name><number>
In the SQL statement:
- •Use a SELECT statement.
- •Do not use an asterisk (*) to select all columns of a table. List the columns that you want to select.
- •Do not use conversion functions, such as TO_CHAR or TO_DATE.
- •Do not use the COUNT function.
Tip: Test the SQL statement you want to use on the source database before you create a saved query. Informatica Cloud does not display specific error messages for invalid SQL statements.