Knowledge Center

Knowledge Center > API > SOAP API > Zuora Object Query Language > Export ZOQL > Export ZOQL Filter Statements

Export ZOQL Filter Statements

Export ZOQL supports multiple filter statements that you can use when building queries. Use Export ZOQL for creating exports from Zuora data sources. Export ZOQL uses a different syntax than standard ZOQL.

Comparison between two fields is not supported by either standard or Export ZOQL.

Filter Statements

Data Type Supported Operators Example Notes
boolean =, !=, is null, is not null select AccountNumber from account where AutoPay != true

Use true or false.

dateTime =, !=, <, >, <=, >=, is null, is not null Datetime with default (Pacific) time zone:

select AccountNumber from account where CreatedDate <= '2008-09-12T05:07:32'

Date with default (Pacific) time zone:

select AccountNumber from account where CreatedDate <= '2008-09-12'

Datetime with Eastern Standard Time:

select AccountNumber from account where CreatedDate <= '2008-09-12T05:07:32-05:00'

Date with GMT:

select AccountNumber from account where CreatedDate <= '2008-09-12Z'

Use the following format for dateTime values (ISO 8601):

YYYY-MM-DDThh:mm:ss.SSSZ

If a date or time is a single-digit number, supply a leading zero (0) as shown in the example.

Several fields are optional, including the seconds (ss) and milliseconds (SSS). As you can see, none of the examples includes milliseconds.

The timezone (Z) is also optional. If you leave it out, then Pacific Time will be used (GMT-8 standard; GMT-7 during daylight savings). Use the letter Z to specify GMT. To specify any other timezone, use an offset from GMT in the form:

+/-hh:mm

To specify only a date, omit the time:

YYYY-MM-DD

Time zones are still treated the same way. If you want to use a time zone other than Pacific Time, specify it in the statement.

See Simplified Dates for more information about using dates with Export ZOQL.

number =, !=, >, <, >=, <=, is null, is not null select AccountNumber from account where BillCycleDay >= 15 Quotes are optional around this value.
string =, !=, >, <, >=, <=, is null, is not null select AccountNumber from account where Status = 'Draft' Strings are enclosed in single quotes: 'like this'
string like, is null, is not null select AccountNumber from account where Name like 'Z%' The string matches the specified query. This can be used to find strings using one of two wildcard characters: the percent character,%, which selects everything, or the underscore character, _, which selects a single character.

For example, the query in the example will return account numbers for all accounts where the value in the Name field starts with the letter Z followed by any characters.

Using null in Filter Statements

You can use null as a value in a filter statement.

For example you can use the following query to find all accounts with a purchase order number:

select AccountNumber from account where PurchaseOrderNumber != null

Similar to SQL, Export ZOQL also supports the use of  is null and is not null.

Filter Relationship Operators

Export ZOQL supports the filter relationship operators and, or, and not.

AND Relationship Operator

The and relationship operator is a logical operator that requires both specified items to be true.

For example, the following query will return IDs only for accounts where AutoPay is set to true and Status is Active.

select AccountNumber from account where AutoPay = true and Status='Active'

OR Relationship Operator

The or relationship operator is a logical operator that requires one or both items to be true.

For example, the following query will return IDs for all accounts whose status is either Draft or Active (or both).

select AccountNumber from account where Status='Draft' or Status='Active'

NOT Relationship Operator

The not relationship operator is a logical operator that requires the specified item to be false.

For example, the following query will return information from all accounts with the name Jill, a variation that includes Jack, and that were not created on January 1, 2011.

select * from Account where name = 'Jill' or name like 'Jack%' or not createdDate = '2011-01-01'

Using Parentheses with OR and NOT

You can use parentheses to nest OR and NOT conditions. For example:

select id from Account where (name like 'Adam %' and (status = 'Active' or status = 'Canceled'))
Last modified
13:01, 7 Aug 2015

Tags

Classifications

(not set)