Creating queries using SQL

If you are an experienced SQL user, you can write your own SQL query or use SQL queries created in other applications by copying and pasting them. Writing raw SQL is an advanced function of Campaign; users are responsible for correct syntax and query results.

Getting correct results when using raw SQL in a Select process (that is, the correct list of audience IDs) requires the use of the <TempTable> token when in-DB optimization is enabled and there is an input cell to the Select process. In addition, to significantly improve performance when Campaign processes very large tables, use the <TempTable> token even when not using in-DB optimization.

A SQL query must return a list of only the unique IDs as defined by the key on a base table.

Your query should use the following syntax:

SELECT DISTINCT(<key1> [<key2>,...]) FROM <table> WHERE <condition> ORDERBY <unique_id>

The query requests the database to perform the sorting and data deduplication. If you omit either the DISTINCT or ORDERBY clause, Campaign will still sort and deduplicate the data on the application server, so you will still receive the correct results, but the performance will likely be slower.

If you have chosen to Select All Customer IDs, you can write raw SQL commands to be run against records in a specified database before or after the Select process is executed.

If you have chosen to apply select criteria to the customer IDs, in addition to the above, you can also enable the use of raw SQL in the Query Builder when creating your select criteria. Otherwise, you can only use IBM® Unica Marketing expressions and Custom Macros.

Building raw SQL statements in Campaign

Using the TempTable and OutputTempTable tokens in raw SQL queries

Referencing Extract tables in raw SQL queries

To create a raw SQL query in the SQL Text Entry Area

To pass multiple commands in a raw SQL statement

To specify pre- or post-processing SQL statements