Creating queries with Formula helper

When you create a query to select IDs in a Select, Segment, or Extract process, you can use the Formula helper to help build the query. The Formula helper is available from the process configuration dialog. The Formula helper contains a set of buttons for inserting commonly used operators and a list of macros, operators, and functions.

Procedure

  1. Begin configuring a process that uses queries, such as Segment, Select, or Extract.
  2. Access the query option for the process:
    • For a Select process, choose Select <audience> IDs with.
    • For a Segment process, choose Segment by query, then either create a segment or double-click a segment to edit it. In the resulting dialog box, choose Select IDs with.
    • For an Extract process, choose Select records with.
  3. Click the Text Builder button to change from the default query method. The button label changes to Point & click.
  4. Click the Formula helper button.

    The Formula helper dialog opens. It contains a set of buttons for inserting commonly used operators and a list of macros, operators, and functions.


    Select process Formula helper
  5. (Optional) To restrict the list to only SQL operators and functions, check SQL.
  6. As you build your query by selecting fields from the Available fields list, use the Formula helper window to help define the query:
    1. Expand the list of macros, operators, or functions to locate the item that you want to use. Select an item to see a description and syntax example. Double-click an item to add it to the query text box.
      Note: For custom macros, the description and syntax were created by the person who wrote the macro.
    2. Use the Formula helper buttons to add operators and punctuation. The Clear button acts as a backspace (erase) key.
    3. You can also edit the query directly. However, you can avoid syntax errors by selecting items, such as field and table names, from the lists provided.
    4. Click Close.
  7. Note that if your query includes a table field that has the same name as a Campaign generated field, you must qualify the field name. Use the following syntax: <table_name>.<field_name>.
  8. Use Check syntax in the process configuration dialog box to detect any errors. Checking the syntax does not put any load on the database server.
  9. (Optional) Use Test query to see how many IDs the query returns.

    A progress bar is displayed while the query is being tested. Close the progress window if you want to cancel the test. When testing is complete, Unica Campaign indicates the number of rows the query returned.

    Important: Global suppressions and cell size limits are not applied in test query counts. Test queries might also return non-normalized data. To obtain an accurate result count, test run the process.