You can set the Is not in query criteria to find what is excluded from a query. Use the Is not in query as follows.
- Send to multiple queries - The send to multiple queries enhancement lets you create multiple queries and then build a query that references each of these queries and some other parameters, and send the creative to those combined queries.
- Use dynamic suppression in your query - Add an Is not in query rule in the query to exclude a segment.
- Build queries with commonly used criteria - You can create a query with commonly used criteria and then reference that query in other queries as you have promotions or messaging that is relevant to that group (or not that group) along with other criteria.
You can use the Is not in query criteria type to select criteria that are not in query to build a list.
- Is not in query is unique to queries.
- Is not in query allows the user to send to multiple queries, suppress dynamically, and leverage commonly used criteria - three totally different applications with one statement. It allows you to reference queries within queries and is available to all (new) queries built against databases.
- Works in both within programs and queries.
- In Programs you can use the criteria type Is not in query within decision diamond rules of your program, but not within the entrance rules of your program.
- The organization must have queries enabled to access Is not in query query criteria.
Not all queries can leverage this criteria. See the types of queries that can and cannot use the Is not in query criteria.
| Can leverage Is not in query criteria | Cannot leverage Is not in query criteria |
|---|---|
| Queries against databases | Classic queries against databases |
| Programs flow criteria queries within programs (like decision diamonds) | Entrance criteria queries within programs |
- From within the query, choose Query from the Profile drop-down.
- Click Choose in the criteria pane, set the Shared/Private location in the pop-up, select the (new) query you want to reference, then click Done.
- Manipulate the criteria the same way you would any other criteria, then finish building your query (or ruleset).
What is the limitation of using the "Is in query" and "Is not in query" operators in a query?
If you create a query referencing other queries by using Is in query and Is not in query operators, consider the limits described here.
Technically there is no limit on how many queries you can reference from one query. However, there is an internal limit of 1,000 fields/columns to be evaluated in a single query, which counts combination of all other queries that this single query is referencing.
For example, you reference 3 other queries in this query criteria:
WHEN Is in query My_Webinar_Query1 AND Is not in query My_Training_Query2 AND Is in query My_walkin_Query3
But if each of the queries above evaluates 400 fields/columns in query criteria, the total number of fields of this query above might exceed 1,000 rows or 32,767 characters, causing the email sent to this query to fail. Here is an example of error message of the failed email, which is caused by this issue.
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error ORA-06512: at 'SDM.MARKETER_SEND_BLOCKING', line 738 ORA-01792: maximum number of columns in a table or view is 1000 ORA-06512: at 'SDM.MARKETER_SEND_MANAGER', line 4793 ORA-06512: at line 1