Guidelines for SQL queries
Writing raw SQL is an advanced operation; users are responsible for correct syntax and query results.
Follow these guidelines when using raw SQL to construct queries in a process configuration:
*
A SQL query must return a list of only the unique IDs as defined by the key on a base table.
*
A SQL query must use the following syntax:
SELECT DISTINCT(<key1> [<key2>,...]) FROM <table> WHERE <condition> ORDERBY <unique_id>
This query instructs the database to perform sorting and data deduplication. If you omit the DISTINCT or ORDERBY clause, Campaign sorts and deduplicates the data on the application server, so you still receive the correct results, but performance will be slower.
*
If in-database optimization is enabled and there is an input cell to the Select process, you must use the <TempTable> token to obtain the correct list of audience IDs.
*
To significantly improve performance with large tables, use the <TempTable> token even when not using in-database optimization.
*
If your database allows multiple commands to be passed, enter as many valid SQL commands as you need, with the following rules:
*
Separate commands with the appropriate delimiter
*
The last command must be a select command.
*
This select command must select all the relevant fields required in defining your audience level in the same order the audience level is defined.
*
No other select statements are used
*
Data filters do not apply to raw SQL queries or to custom macros that use raw SQL. To learn about data filters, see the IBM® Marketing Platform Administrator's Guide.
Copyright IBM Corporation 2015. All Rights Reserved.