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\>
ElementsDescription
QUERY_NAMEThe name of the new query.
PARENT_LIST_IDThe 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
CRITERIADescribes the expressions to perform on one or more columns in the database.
EXPRESSIONDescribes 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>
SUCCESSTRUE if successful


TRUE
25874


 

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

Operation
ElementsQUERY_NAMEThe name of the new query.
PARENT_LIST_IDThe ID of the database being queried.
VISIBILITYDefining the visibility of the new query. Valid values are:
- 0 – Private
- 1 – Shared
PARENT_FOLDER_IDOptionalThe 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_COLUMNSOptionalA pipe-separated list of fields available for personalization.
ALLOW_FIELD_CHANGEOptionalDefines 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 ElementEXPRESSIONDescribes the expressions to perform on one or more columns in the database.
CRITERIASpecifies if criteria are locked or editable. Valid values are:

- Locked
- Editable.

If not specified, the default is marked Editable.
Child ElementOptionalEXPRESSIONDescribes a single expression to perform on a column. One or more EXPRESSION elements can exist within the CRITERIA element.
OptionalTYPEThe 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.
ElementCOLUMN_NAMEOptionalThe name of the database field.
Note: COLUMN_NAME is required when defining database expressions but not specified for relational table expressions.
OPERATORSOptionalThe 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.
VALUESOptionalThe 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_IDOptionalThe ID of the Relational Table when one or more relational table expressions are being defined.
LEFT_PARENSOptionalUsed to specify one or more left parentheses when relating more than one expression. For example, two left parentheses would be indicated as: ((
RIGHT_PARENSOptionalUsed to specify one or more right parentheses when relating more than one expression. For example, two right parentheses would be
indicated as: ))
AND_OROptionalUsed to specify AND or OR when relating multiple expressions.
RT_EXPRESSIONOptionalDescribes 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.
ChildElementsTYPEThe type of evaluation. Valid values are:
- NE – Numeric
- DE – Date/Time
- TE – Text
COLUMN_NAMEThe name of the relational table field.
OPERATORSThe operator that is used for comparison. Valid values are: != < > = IS NOT null IS null LIKE NOT like IN NOT in BETWEEN
VALUESOptionalThe 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_PARENSOptionalUsed to specify one or more left parentheses when relating more than
one expression. For example, two left parentheses would be indicated as: ((
RIGHT_PARENSOptionalUsed 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.
BEHAVIOROptionalOptional; defines the Behavior section of the query. Filters mailing
contacts by their activity.
OPTIONAL_OPERATORSpecifies the operation or activity for which you are searching. Valid values are: 100– Opened 101 – Clicked 102 – Bounced 103 – No Activity
TYPE_OPERATORSpecifies 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_IDOptionalThe ID of the mailing within which to search for contact activity.
REPORT_IDOptionalRequired when using the MAILING_ID parameter. The Report ID that identifies the mailing instance within which to search for contact activity.
LINK_NAMEOptionalThe name of a link in a mailing. Used to include only contacts who clicked a particular link in a mailing.
WHERE_OPERATOROptionalSpecifies 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_OPERATOROptionalThe operator that is used for comparison. Required if WHERE_OPERATOR is included. Valid values are: != < > = >= <=
BETWEEN
VALUESOptionalThe 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
ElementsSUCCESSTRUE if successful
ListIDThe 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>