Create a database query - CreateQuery or Classic Query

Prerequisites

A query can search for values within database columns, relational table
columns, and also filter by mailing activity.

Note: Updates were made to this call to allow users to use the below
criteria in the CreateQuery call.

Create a database query using behavioral query criteria: Examples of Behavioral criteria that can be used within the CreateQuery API call to create a query of an Acoustic Campaign database.
Create a Query of a database by using Multiple Query
Expressions: Examples of Multiple query expressions that can be used within the CreateQuery API call to build a query that contains data filters and search conditions that define the scope of an Acoustic Campaign database search.
Create a database query using time-based operators: Examples of time-based operators that can be used within the CreateQuery API call to build a query that uses time-based operators to pull information for a specific timeframe within an Acoustic Campaign query.
Create a database query using universal behavior criteria: Examples of Universal Behavior criteria that can be used within the CreateQuery API call to show if a behavior was or was not attained for a specific timeframe within an Acoustic Campaign query.
Create a database query using relational table criteria: Examples of relational table criteria that can be used within the CreateQuery API call to pull a range of data from an Acoustic Campaign relational table.
Create a database query using profile criteria: Examples of profile-based operators that can be used within the CreateQuery API call to show exact or a range of data within an Acoustic Campaign query.

Syntax of the request and response XML for CreateQuery {#tocstep1 .makeTOC}

Operation: \<CreateQuery\>

Elements	Description
QUERY_NAME	The name of the new query.
PARENT_LIST_ID	The ID of the database being queried.
PARENT_FOLDER_ID (Optional)	The ID of the folder within which to save the query. If not specified, it is stored in the root of Shared or Private based on VISIBILITY.
SELECT_COLUMNS (Optional)	A pipe-separated list of fields available for personalization.	ONLY HONORED BY CLASSIC QUERIES
ALLOW_FIELD_CHANGE (Optional)	Defines whether to allow users to change the fields available for personalization when editing or creating a new query from this query. If not specified, the default is 1 (Allow Changes). Valid values are: 0 – Do not allow changes 1 – Allow Changes.	ONLY HONORED BY CLASSIC QUERIES
CHILD ELEMENTS	Description
CRITERIA	Describes the expressions to perform on one or more columns in the database.
EXPRESSION	Describes a single expression to perform on a column. One or more EXPRESSION elements can exist within the CRITERIA element.
LOCKED_EXPRESSION (Optional)	Only one of these elements can exist.	<LOCKED_EXPRESSION criteria_type=’profile’> is in query [another query id] </LOCKED_EXPRESSION>
COLUMN (Optional)	Column name of the database or relational table. Required for ‘profile’ and ‘rt_profile’ criteria. Attribute name for universal behavior elements
OPERATOR (Required)	- is blank - is not blank - anniversary is - today is - is after - is not after - is before - is not before - contains - does not contain - starts with - does not start with - ends with - does not end with - is equal to - is not equal to - is less than - is not less than or equal to - is greater than - is greater than or equal to - is after - is before - year is equal to - month is equal to - day is equal to - is equal to one of the following - is not equal to one of the following - contains one of the following - does not contain one of the following - starts with one of the following - does not start with one of the following - is between - is within the last - is not within the last - is within the next - is not within the next - is exactly - is not exactly - is within - is not within - is within the last range of - is in contact list - is not in contact list - is in query - is not in query Available operators for ‘relational_table’ criteria type: - match - not match Available operators for ‘behavior’ criteria type: - has submitted web form - has not submitted web form - has visited web site - has not visited web site - have visited web page - has not visited web page - has triggered custom tracking event - has not triggered custom tracking event - has been sent email - has not been sent email - has clicked link in email - has not clicked link in email - has opened email - has not opened email - has attained ub event - has not attained ub event - has downloaded file - has not downloaded file - has been in program - has not been in program
VALUE (Optional)	Required when using one of the following operators: - contains - does not contain - starts with - does not start with - ends with - does not end with - is equal to - is not equal to - is less than - is less than or equal to - is greater than - is greater than or equal to - is after - is before - year is equal to - month is equal to - day is equal to
TIMEFRAME (Optional)	These are the following timeframes that can be used: - at any time - on today - before today - before today on - before - after - between - within - the last - exactly - more than - less than	These are the following operators that can use timeframes: - has submitted web form - has not submitted web form - has visited web site - has not visited web site - has visited web page - has not visited web page - has triggered custom tracking event - has not triggered custom tracking event - has been sent email - has not been sent email - has clicked link in email - has not clicked link in email - has opened email - has not opened email - has attained ub event - has not attained ub event - has downloaded file - has not downloaded file - is in query - is not in query - has been in program - has not been in program - is in stages - is not in stages
TIME_UNIT (Optional)	These are the following time units that can be used: - days - weeks - months	Can be used with these operators: - is within the last - is not within the last - is within the next - is not within the next - is exactly - is not exactly - anniversary is - is within - is not within - is within the last range of Can be used with these time frames: - within the last - exactly - more than - less than
UNIT	- ago - away	Used with TIME_UNIT and the following operators: - is exactly - is not exactly - anniversary is And the following time frames: - exactly - more than - less than
PARENS (Optional)	Used to specify one or more left/right parentheses when relating more than one expression.
CONJUNCTION (Optional)	Used to specify AND or OR when relating multiple expressions.	AND/OR
RT_EXPRESSIONS (Optional)	Child element to an EXPRESSION element when criteria_type=’relational_table’. This element itself can have child EXPRESSION elements with criteria_type=’rt_profile’	Example RT expression: match/not match</OPERATOR> [rt id]<ID> <RT_EXPRESSIONS> [any valid elements for profile criteria] </RT_EXPRESSIONS>
CHANNEL (Optional)	Channel Type: PUSH SMS EMAIL Used with the following operators: NEVER-JOINED, OPTED-OUT, OPTED-IN	NEVER-JOINED/OPTED-OUT/OPTED-IN SMS/PUSH</CHANNEL> [channel qualifier 1] [channel qualifier 2] or true
UB_EXPRESSIONS (Optional)	Child element to an EXPRESSION element when criteria_type=’behavior’ and operator is UB operator. This element itself can have child EXPRESSION elements with criteria_type=’behavior’	has attained ub event/has not attained ub event </ID> is exactly 10 <TIME_UNIT>days</TIME_UNIT> ago <UB_EXPRESSIONS> [any profile operator] [attribute name </UB_EXPRESSIONS>
EMAIL_LINK (Optional)	Used with behavior criteria and the following operators: - has clicked link in email - has not clicked link in email	has clicked link in email [mailing id] <EMAIL_LINK>[link name]</EMAIL_LINK> is within the last 10</VALUE> <TIME_UNIT>weeks</TIME_UNIT>
EMAIL_LINKS (Optional)	Used with behavior criteria and the following operators: - has clicked any link - has not clicked link in email Note that with any link it goes from email_link to email_linkS. <EMAIL_LINK>isAnyLink</EMAIL_LINK> would not work. <EMAIL_LINKS isAnyLink=”true”/>is the correct way to build it. If you were still building a query of a specific link click(s), then you would continue to use EMAIL_LINK	has clicked link in email 12345 <EMAIL_LINKSisAnyLink=”true”/> on today
WEB_FORM_NAME (Optional)	Used with behavior criteria and the following operators: - has submitted web form - has not submitted web form	has submitted web form <WEB_FORM_NAME>[web form name]</WEB_FORM_NAME> on today/TIMEFRAME>
WEB_SITE (Optional)	Used with behavior criteria and the following operators: - has visited web site - has not visited web site	has submitted web form <WEB_SITE>[website/domain name]</WEB_SITE> at any time
WEB_PAGE (Optional)	Used with behavior criteria and the following operators: - has visited web page - has not visited web page	has visited web page <WEB_PAGE>[web page]</WEB_PAGE> at any time
FILE (Optional)	Used with behavior criteria and the following operators: - has downloaded file - Has not downloaded file	has downloaded file [file path] at any time
EVENT_NAME (Optional)	Used with behavior criteria and the following operators: - has triggered custom tracking event - has not triggered custom tracking event	<OPERATOR>has triggered custom tracking event <EVENT_NAME>[file path]</EVENT_NAME> <FRIENDLY_NAME>[friendly name] or ANY element</FRIENDLY_NAME> at any time
FRIENDLY_NAME (Optional)	Used with behavior criteria and the following operators: - has triggered custom tracking event - has not triggered custom tracking event	has triggered custom tracking event <EVENT_NAME>[file path]</EVENT_NAME> <FRIENDLY_NAME>[friendly name] or ANY element</FRIENDLY\NAME> at any time
ANY (Optional)	Can be used as child element of the following elements: FRIENDLY_NAME VALUES	true/false
ID (Optional)	Used with the following operators: - is in contact list - is not in contact list - is in query - is not in query - has been sent email - has not been sent email - has clicked link in email - has not opened email - has opened email - has attained ub event - has no attained ub event - has been in program - has not been in program - match - not match	has been in program [program id] at any time is in query [query id] at any time is in contact list</OPERATOR> [contact list id] has been sent email/has opened email [mailing id] at any time
TABLE_ID (Optional)	Used for child relational table expressions
Response: <RESULT>
SUCCESS	TRUE if successful	TRUE 25874

This interface supports programmatically creating a Classic query of an Acoustic Campaign database.

Operation
Elements	QUERY_NAME	The name of the new query.
	PARENT_LIST_ID	The ID of the database being queried.
	VISIBILITY	Defining the visibility of the new query. Valid values are: - 0 – Private - 1 – Shared
	PARENT_FOLDER_ID	Optional	The ID of the folder within which to save the query. If not specified, it is stored in the root of Shared or Private based on VISIBILITY.
	SELECT_COLUMNS	Optional	A pipe-separated list of fields available for personalization.
	ALLOW_FIELD_CHANGE	Optional	Defines whether to allow users to change the fields available for personalization when editing or creating a new query from this query. If not specified, the default is 1 (Allow Changes). Valid values are: - 0 – Do not allow changes. - 1 – Allow Changes
	Child Element	EXPRESSION	Describes the expressions to perform on one or more columns in the database.
		CRITERIA	Specifies if criteria are locked or editable. Valid values are: - Locked - Editable. If not specified, the default is marked Editable.
Child Element	Optional	EXPRESSION	Describes a single expression to perform on a column. One or more EXPRESSION elements can exist within the CRITERIA element.
	Optional	TYPE	The type of evaluation. Valid values are: NE – Numeric DE – Date/Time TE – Text: - NE – Numeric - DE – Date/Time - TE – Text Note: TYPE is required when defining database expressions but not specified for relational table expressions.
Element	COLUMN_NAME	Optional	The name of the database field. Note: COLUMN_NAME is required when defining database expressions but not specified for relational table expressions.
	OPERATORS	Optional	The operator that is used for comparison. Valid values are: != < > = IS NOT null IS null LIKE NOT like IN NOT in BETWEENOPERATORS is required when defining database expressions but not specified for relational table expressions. Note: OPERATORS is required when defining database expressions but not specified for relational table expressions.
	VALUES	Optional	The value being compared to the Acoustic Campaign database field. This can include text or a numeric or date value. It can also include a list of values or the name of a Column. If specifying a database column, surround with square brackets, such as: [My TextColumn]. If specifying a list of values, use the pipe character ( \| ) to separate values in a list. Required for all operators other than IS NOT null and IS null. Note: VALUES is not specified for relational table expressions.
	TABLE_ID	Optional	The ID of the Relational Table when one or more relational table expressions are being defined.
	LEFT_PARENS	Optional	Used to specify one or more left parentheses when relating more than one expression. For example, two left parentheses would be indicated as: ((
	RIGHT_PARENS	Optional	Used to specify one or more right parentheses when relating more than one expression. For example, two right parentheses would be indicated as: ))
	AND_OR	Optional	Used to specify AND or OR when relating multiple expressions.
	RT_EXPRESSION	Optional	Describes a single expression to perform on a relational table column. One or more RT_EXPRESSION elements can exist within the EXPRESSION element. Note: The RT_EXPRESSION child elements are the same for database criteria and relational table criteria. See EXPRESSION section within the CRITERIA section for definition.
	ChildElements	TYPE	The type of evaluation. Valid values are: - NE – Numeric - DE – Date/Time - TE – Text
		COLUMN_NAME	The name of the relational table field.
		OPERATORS	The operator that is used for comparison. Valid values are: != < > = IS NOT null IS null LIKE NOT like IN NOT in BETWEEN
	VALUES	Optional	The value being compared to the Acoustic Campaign relational table field. This can include text or a numeric or date value. It can also include a list of values or the name of a Column. If specifying a Column, surround with square brackets, such as: [My Text Column]. If specifying a list of values, use the pipe character ( \| ) to separate values in a list. Required for all operators other than IS NOT null and IS null.

	LEFT_PARENS	Optional	Used to specify one or more left parentheses when relating more than one expression. For example, two left parentheses would be indicated as: ((
	RIGHT_PARENS	Optional	Used to specify one or more right parentheses when relating more than one expression. For example, two right parentheses would be indicated as: ))
	AND_OR	Optional*	Used to specify AND or OR when relating multiple expressions.

	BEHAVIOR	Optional	Optional; defines the Behavior section of the query. Filters mailing contacts by their activity.
	OPTIONAL_OPERATOR	Specifies the operation or activity for which you are searching. Valid values are: 100– Opened 101 – Clicked 102 – Bounced 103 – No Activity
	TYPE_OPERATOR	Specifies whether to search a particular mailing (or all mailings) for the database. Valid values are: 110 – For Any Mailing (only available if query is on the database that is designated to store Automated Behavior Updates) 111 – For One Mailing
	MAILING_ID	Optional	The ID of the mailing within which to search for contact activity.
	REPORT_ID	Optional	Required when using the MAILING_ID parameter. The Report ID that identifies the mailing instance within which to search for contact activity.
	LINK_NAME	Optional	The name of a link in a mailing. Used to include only contacts who clicked a particular link in a mailing.
	WHERE_OPERATOR	Optional	Specifies whether to compare to an activity count, date of activity, or number of days since activity. Valid values are: 120 – Days Since Activity 121 – Activity Date 122 – Activity Count Only used when you specify For One Mailing and you do NOT include a No Activity parameter.
	CRITERIA_OPERATOR	Optional	The operator that is used for comparison. Required if WHERE_OPERATOR is included. Valid values are: != < > = >= <= BETWEEN
	VALUES	Optional	The value that you are comparing to the contact activity count, date of activity, or number of days since activity. Might be numeric or date value. If operator is BETWEEN, two values must be provided delimited by a pipe. Required if WHERE_OPERATOR is included.
Example	<QUERY_NAME>Has_Mobile_30_Zip</QUERY_NAME> <PARENT_LIST_ID>17607</PARENT_LIST_ID> 0 <PARENT_FOLDER_ID>9512</PARENT_FOLDER_ID> <ALLOW_FIELD_CHANGE>0</ALLOW_FIELD_CHANGE> <SELECT_COLUMNS>Last_Name\|First_Name</SELECT_COLUMNS> editable TE <COLUMN_NAME>Zip</COLUMN_NAME> <LEFT_PARENS>(</LEFT_PARENS> <AND_OR>AND</AND_OR> TE <COLUMN_NAME>Mobile</COLUMN_NAME> <RIGHT_PARENS>)</RIGHT_PARENS> <TABLE_ID>4645</TABLE_ID> < AND_OR>AND</AND_OR> <LEFT_PARENS>(</LEFT_PARENS> <RT_EXPRESSION> TE <COLUMN_NAME>Purchase Type</COLUMN_NAME> <LEFT_PARENS>(</LEFT_PARENS> </RT_EXPRESSION> <RT_EXPRESSION> <AND_OR>AND</AND_OR> DE <COLUMN_NAME>Purchase Date</COLUMN_NAME> ]]> <RIGHT_PARENS>)</RIGHT_PARENS> </RT_EXPRESSION> <TABLE_ID>8581</TABLE_ID> <AND_OR>OR</AND_OR> <RIGHT_PARENS>)</RIGHT_PARENS> <RT_EXPRESSION> TE <COLUMN_NAME>Expired</COLUMN_NAME> </RT_EXPRESSION> <OPTION_OPERATOR>103</OPTION_OPERATOR> <TYPE_OPERATOR>111</TYPE_OPERATOR> <MAILING_ID>669614</MAILING_ID> <REPORT_ID>502993</REPORT_ID>
Response
Elements	SUCCESS	TRUE if successful
	ListID	The ID of the query that was created.
Example	TRUE 25874

Create a database query using universal behavior criteria

Use Universal Behavior based operators to show if a behavior was or was not attained for a specific timeframe within a Query.

The table shows the Universal Behavior criteria that is now available for Create Query.

Criteria

Query Description	Criteria Type	Operator for the UB	Attribute Name	Attribute Operator	Attribute Value	Timeframe
test has attained UB event with attribute	behavior	has attained ub event	Text	is equal to	Bob	at any time
test has not attained UB event with attribute	behavior	has not attained ub event	Text	contains one of the following	Bob, Susan	at any time
test has attained UB event with attribute	behavior	has attained ub event	Numeric	is between	3, 5	at any time
test has not attained UB event with attribute	behavior	has not attained ub event	Date	is within the last	5 days	at any time
test has attained UB event with attribute	behavior	has attained ub event	Date	is within the last range of	3-5 Days	at any time
test has attained UB event with attribute	behavior	has attained ub event	Date	is today		at any time
test has not attained UB event with attribute	behavior	has not attained ub event				at any time

Create a database query using multiple query expressions

Prerequisites

Multiple Query Expressions allow a user to build a query that contains data filters and search conditions that define the scope of a database search.

Criteria type and an example calls for Multiple Query Expressions

Criteria Type	And/Or	Left Parens	Column Name	Operator	Values	Right Parens
profile	AND	(	first name	is blank
profile	OR		age	is not equal to	10
profile	OR	(	zipcode	is equal to one of the following	30052, 30033
profile	AND		anniversary	is not within the last	5 days	))

Example Calls

The criteria creates editable criteria. When Email is not blank OR Email is equal to [email protected]

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q2</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION criteria_type="profile">
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>is not blank</OPERATOR>
        <COLUMN>Email</COLUMN>
      </EXPRESSION>
      <EXPRESSION criteria_type="profile">
        <CONJUNCTION>OR</CONJUNCTION>
        <OPERATOR>is equal to</OPERATOR>
        <COLUMN>Email</COLUMN>
        <WEB_SITE>http://www.ibm.com</WEB_SITE>
        <VALUE>[email protected]</VALUE>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Multiple Query Expression Example with Parenthesis

This criteria creates Editable Criteria:

When Email is not blank

OR (Email is not blank

AND Email is not blank)

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q2</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION criteria_type="profile">
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>is not blank</OPERATOR>
        <COLUMN>Email</COLUMN>
      </EXPRESSION>
      <EXPRESSION criteria_type="profile">
        <PARENS>(</PARENS>
        <CONJUNCTION>OR</CONJUNCTION>
        <OPERATOR>is not blank</OPERATOR>
        <COLUMN>Email</COLUMN>
      </EXPRESSION>
      <EXPRESSION criteria_type="profile">
        <PARENS>)</PARENS>
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>is not blank</OPERATOR>
        <COLUMN>Email</COLUMN>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Create database query using profile criteria

Use Profile based operators to show exact or a range of data within a Query.

The table shows the Profile criteria that is now available for Create Query.

Criteria

operator	Column Name	values	Channel	Locked Query Id/Name
is blank	first name			someOtherQueryId
is not blank	first name
is between	age	10 and 20
is equal to	age	10
is not equal to	age	10		someOtherQueryId
is equal to one of the following	zipcode	30052, 30033
is not equal to one of the following	zipcode	30052, 30033
is within the last range of	anniversary	5 and 10 days
is within the next	anniversary	10 days		someOtherQueryId
is not within the last	anniversary	5 days
is in contact list		list id or name
is not in contact list		list id or name		someOtherQueryId
is not in query		someOtherQueryId
OPTED-IN		any qualifier	PUSH
OPTED-OUT		single qualifier	SMS
NEVER-JOINED		multiple qualifiers	PUSH

Create a database query using time-based operators

Use Time based operators to pull information for a specific timeframe within a Query.

Examples of API Calls for Create Query by using various time-based operators.

Example Calls

Contains “on today” new query criteria

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q10</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION criteria_type="behavior">
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>has visited web page</OPERATOR>
        <WEB_PAGE>http://www.ibm.com</WEB_PAGE>
        <TIMEFRAME>on today</TIMEFRAME>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Contains “Less Than or More Than” new query criteria

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q10</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION criteria_type="behavior">
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>has visited web page</OPERATOR>
        <WEB_PAGE>http://www.ibm.com</WEB_PAGE>
        <TIMEFRAME>less than</TIMEFRAME>
        <VALUE>80</VALUE>
        <TIME_UNIT>days</TIME_UNIT>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Contains “Within the last” new query criteria

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q10</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION criteria_type="behavior">
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>has visited web page</OPERATOR>
        <WEB_PAGE>http://www.ibm.com</WEB_PAGE>
        <TIMEFRAME>within the last</TIMEFRAME>
        <VALUE>80</VALUE>
        <TIME_UNIT>days</TIME_UNIT>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Contains “Before Today” new query criteria

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q10</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION criteria_type="behavior">
        <CONJUNCTION>AND</CONJUNCTION>
        <OPERATOR>has visited web page</OPERATOR>
        <WEB_PAGE>http://www.ibm.com</WEB_PAGE>
        <TIMEFRAME>before today</TIMEFRAME>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Create a database query using universal behavior criteria

Use Universal Behavior based operators to show if a behavior was or was not attained for a specific timeframe within a Query.

The table shows the Universal Behavior criteria that is now available for Create Query.

Criteria

Query Description	Criteria Type	Operator for the UB	Attribute Name	Attribute Operator	Attribute Value	Timeframe
test has attained UB event with attribute	behavior	has attained ub event	Text	is equal to	Bob	at any time
test has not attained UB event with attribute	behavior	has not attained ub event	Text	contains one of the following	Bob, Susan	at any time
test has attained UB event with attribute	behavior	has attained ub event	Numeric	is between	3, 5	at any time
test has not attained UB event with attribute	behavior	has not attained ub event	Date	is within the last	5 days	at any time
test has attained UB event with attribute	behavior	has attained ub event	Date	is within the last range of	3-5 Days	at any time
test has attained UB event with attribute	behavior	has attained ub event	Date	is today		at any time
test has not attained UB event with attribute	behavior	has not attained ub event				at any time

Create a database query using relational table criteria

Use Relational Table criteria within the Create Query API call to pull a range of data from a Relational Table.

Overview

Criteria type and example calls that use Relational Table criteria.

Relational Table Criteria

Operator	Column Name	Values
is blank	first name
is not blank	first name
is between	age	10 and 20
is equal to	age	10
is not equal to	age	10
is equal to one of the following	zipcode	30052, 30033
is not equal to one of the following	zipcode	30052, 30033
is within the last range of	anniversary	5 and 10 days
is within the next	anniversary	10 days
is not within the last	anniversary	5 days

Example Call Number 1

Using Relational Table Criteria

<Envelope>
  <Body>
    <CreateQuery>
      <PARENT_LIST_ID>348328</PARENT_LIST_ID>
      <QUERY_NAME>xli_test_create_Q7</QUERY_NAME>
      <VISIBILITY>1</VISIBILITY>
      <CRITERIA>
        <EXPRESSION criteria_type="rt_profile">
          <CONJUNCTION>AND</CONJUNCTION>
          <ID>348449</ID>
          <OPERATOR>match</OPERATOR>
          <RT_EXPRESSIONS>
            <EXPRESSION criteria_type="rt_profile">
              <COLUMN>age</COLUMN>
              <CONJUNCTION>AND</CONJUNCTION>
              <OPERATOR>is blank</OPERATOR>
            </EXPRESSION>
          </RT_EXPRESSIONS>
        </EXPRESSION>
      </CRITERIA>
    </CreateQuery>
  </Body>
</Envelope>

This criteria creates Editable Criteria:

WHEN

In Relational Table: testRt1

WHEN age is blank

Example Call Number 2

<Envelope>
  <Body>
    <CreateQuery>
      <QUERY_NAME>Females who purchased single item greater than 100</QUERY_NAME>
      <PARENT_LIST_ID>2740754</PARENT_LIST_ID>
      <VISIBILITY>1</VISIBILITY>
      <CRITERIA>
        <EXPRESSION criteria_type="profile">
          <CONJUNCTION>AND</CONJUNCTION>
          <PARENS>(</PARENS>
          <OPERATOR>is equal to</OPERATOR>
          <COLUMN>Gender</COLUMN>
          <VALUE>
            <![CDATA[Female]]>
          </VALUE>
        </EXPRESSION>
        <EXPRESSION criteria_type="profile">
          <CONJUNCTION>AND</CONJUNCTION>
          <OPERATOR>is equal to one of the following</OPERATOR>
          <COLUMN>Zip Code</COLUMN>
          <VALUES>
            <VALUE>
              <![CDATA[30318]]>
            </VALUE>
            <VALUE>
              <![CDATA[30306]]>
            </VALUE>
            <VALUE>
              <![CDATA[30308]]>
            </VALUE>
            <VALUE>
              <![CDATA[30314]]>
            </VALUE>
          </VALUES>
          <PARENS>)</PARENS>
        </EXPRESSION>
        <EXPRESSION criteria_type="relational_table">
          <CONJUNCTION>AND</CONJUNCTION>
          <OPERATOR>match</OPERATOR>
          <ID>9864803</ID>
          <RT_EXPRESSIONS>
            <EXPRESSION criteria_type="rt_profile">
              <CONJUNCTION>AND</CONJUNCTION>
              <OPERATOR>is today</OPERATOR>
              <COLUMN>Date Purchased</COLUMN>
            </EXPRESSION>
            <EXPRESSION criteria_type="rt_profile">
              <CONJUNCTION>AND</CONJUNCTION>
              <OPERATOR>is greater than</OPERATOR>
              <COLUMN>Item Price</COLUMN>
              <VALUE>
                <![CDATA[100.00]]>
              </VALUE>
            </EXPRESSION>
          </RT_EXPRESSIONS>
        </EXPRESSION>
      </CRITERIA>
    </CreateQuery>
  </Body>
</Envelope>

This would result in a Database Query containing profile criteria and RT criteria

WHEN (Gender is equal to Female
AND Zip Code is equal to one of the following (30318 | 30306 | 30308 | 30314))
AND
Relational Table: Orders
Date Purchased is today
AND Item Price is greater than 100.00

When Will this Call Fail?

This call fails when:

The Call is malformed.
Missing required elements such as the Visibility field.
Query name is already in use.

Example Error Malformed Call and Response

<Envelope>
  <Body>
    <CreateQuery>
    <PARENT_LIST_ID>348328</PARENT_LIST_ID>
    <QUERY_NAME>xli_test_create_Q8</QUERY_NAME>
    <VISIBILITY>1</VISIBILITY>
    <CRITERIA>
      <EXPRESSION
        criteria_type="relational_table">
      <CONJUNCTION>AND</CONJUNCTION>
      <ID>348449</ID>
      <OPERATOR>not match</OPERATOR>
      <RT_EXPRESSIONS>
      <EXPRESSION criteria_type="rt_profile">
      <COLUMN>age</COLUMN>
      <CONJUNCTION>AND</CONJUNCTION>
      <OPERATOR>is blank</OPERATOR>
      <EXPRESSION>
        </RT_EXPRESSION>
      </EXPRESSION>
    </CRITERIA>
    <CreateQuery>
  </Body>
</Envelope>

Response

<Envelope>
  <Body>
    <RESULT>
      <SUCCESS>false</SUCCESS>
    </RESULT>
    <Fault>
      <Request/>
      <FaultCode/>
      <FaultString>Invalid XML Request</FaultString>
      <detail>
        <error>
          <errorid>51</errorid>
          <module/>
          <class>SP.API</class>
          <method/>
        </error>
      </detail>
    </Fault>
  </Body>
</Envelope>

Example Error Response: Missing Visible Field

<Envelope>
  <Body>
    <RESULT>
      <SUCCESS>false</SUCCESS>
    </RESULT>
    <Fault>
      <Request/>
      <FaultCode/>
      <FaultString>
        <! [CDATA[ Parameter VISIBILITY was not provided in API call ]]>
      </FaultString>
      <detail>
        <error>
          <errorid>100</errorid>
          <module/>
          <class>SP.API</class>
          <method/>
        </error>
      </detail>
    </Fault>
  </Body>
</Envelope>

Example Error Message for Query name that exists

<Envelope>
  <Body>
    <RESULT>
      <SUCCESS>false</SUCCESS>
    </RESULT>
    <Fault>
      <Request/>
      <FaultCode/>
      <FaultString>
        <! [CDATA[ A List With This Name (xli_test_create_Q1 Already Exists. ]]>
      </FaultString>
      <detail>
        <error>
          <errorid>256</errorid>
          <module/>
          <class>SP.ListManager</class>
          <method/>
        </error>
      </detail>
    </Fault>
  </Body>
</Envelope>