Creating raw SQL queries

When you configure a Select, Segment, or Extract process in a flowchart, you can use queries to identify contacts from your databases or flat files. The Select, Segment, and Extract processes provide query builders to help you construct queries. Additionally, experienced SQL users can write their own SQL queries or copy and paste SQL queries from other applications. Writing raw SQL is an advanced operation; users are responsible for correct syntax and query results.

Procedure

  1. Begin configuring a Select, Segment, or Extract process.
  2. For a Select process:
    1. Choose Select <audience> IDs with.
    2. The button under the Expression box must say Point & click. If it does not, click the Text Builder button so the label changes to Point & click.
    3. Click Advanced.
    4. In the Advanced settings dialog, check Use raw SQL for record selection. This option enables the use of raw SQL in the Text Builder when you specify your selection criteria. If you do not select this option, you can only use Unica expressions and custom macros.
    5. Use the Database list to select a data source to query. Select a target audience from the Audience level list.
    6. If you want to run SQL commands before or after the Select process, you can specify raw SQL in the Pre-processing or Post-processing areas. See Specifying pre- or post-processing SQL statements.
    7. Click OK to close the Advanced Settings dialog.
    8. Enter raw SQL in the Expression area. You can use the Formula helper to help construct the SQL. Check SQL in the Formula helper to restrict the list of operators and functions to SQL-specific options.
  3. For a Segment process:
    1. On the Segment tab, choose Segment by query.
    2. Click New segment to create a segment, or select an existing segment in the list and click Edit.
    3. In the New segment or Edit segment dialog, choose Select <audience> IDs with, click Text Builder, then click Advanced.
    4. In the Advanced settings dialog, check Use raw SQL, select a database, and click OK.
    5. Enter raw SQL in the Expression area. Optionally, use the Formula helper to help construct the SQL. Check SQL in the Formula helper to restrict the list of operators and functions to SQL-specific options.
  4. For an Extract process:
    1. Choose Select <audience> IDs with.
    2. The button under the Expression box must say Point & click. If it does not, click the Text Builder button so the label changes to Point & click.
    3. Enter raw SQL in the Expression area. You can use the Formula helper to help construct the SQL. Check SQL in the Formula helper to restrict the list of operators and functions to SQL-specific options.
  5. 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>.