Skip to main content

Export ZOQL Filter Statements

Zuora

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 time zone:

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

Date with default 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'

Note: If you are using WSDL 69 or later, the default time zone is the time zone of your tenant. If you are using earlier WSDL versions, the default time zone is the Pacific time zone.

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

YYYY-MM-DDThh:mm:ssZ

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

The second (ss) field is optional. As you can see, none of the examples includes milliseconds.

The time zone (Z) is also optional. If you leave it out, the default time zone will be used. Use the letter Z to specify GMT. To specify any other time zone, 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 the default time zone, 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'))