Example: Using the TempTable and OutputTempTable tokens

This example shows how to use TempTable and OutputTempTable tokens in a raw SQL query.

Assume that you have a Select1 process that selects 10,000 customers who are "Gold" customers (for example, Indiv.AcctType = 'Gold'). You then connect Select1 to a second Select process ("Select2") using a raw SQL query:

Select p.CustID from Indiv p, <TempTable> where p.CustID = <TempTable>.CustID group by p.CustID having sum(p.PurchAmt) > 500

This example selects customers the sum of whose purchases exceeds $500 and who are in the input cell (in other words, customers who have a "Gold" account type).

In contrast, a raw SQL query omitting the <TempTable> token and join:

Select p.CustID from Purchases p group by p.CustID having sum(p.PurchAmt) > 500

first calculates the sum of purchases for all customers in the Purchases table (which could be millions of customers) and then selects all customers the sum of whose purchases exceed $500, regardless of whether they are "Gold" customers or not.

Therefore, for best performance, even if in-DB optimization is disabled, write your raw SQL queries using the <TempTable> token when there is an input cell.

For simplicity, this example does not use the <OutputTempTable> token, but to maintain in-DB optimization and prevent the audience IDs from being retrieved from the database back to the Unica Campaign server, you must include the <OutputTempTable> token in your raw SQL query. For example:

Create table <OutputTempTable> as Select p.CustID from Purchases p, <TempTable> where p.CustID = <TempTable>.CustID group by p.CustID having sum(p.PurchAmt) > 500