Guidelines for using raw SQL queries

Writing raw SQL is an advanced operation; users are responsible for correct syntax and query results.

Note: The Use raw SQL permission for user tables in Unica Campaign determines who can use raw SQL. It is strongly recommended that administrators restrict the ability to use raw SQL to a user who has proper ACL privileges within the organization. For important information, read about administrative permissions in the Unica Campaign Administrator's Guide.
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, Unica 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 Unica Platform Administrator's Guide.