Create a database query - CreateQuery or Classic Query

Used to create a query of a Acoustic Campaign database.

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-INSMS/PUSH\[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\is exactly10daysago[any profile operator][attribute name

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] [link name] is within the last 10\ weeks

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 email12345on 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[website/domain name]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]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

has triggered custom tracking event [file path][friendly name] or ANY element\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[file path][friendly name] or ANY element 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 timeis in contact list\[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

TRUE25874

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

Has_Mobile_30_Zip 17607 0 9512 0 Last_Name|First_Name editable TE Zip ( AND TE Mobile ) 4645 < AND_OR>AND ( TE Purchase Type ( AND DE Purchase Date ]]> ) 8581 OR ) TE Expired 103 111 669614 502993

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

))

  1. 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>