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.
|Data Type||Supported Operators||Example||Notes|
|boolean||=, !=, is null, is not null|| || |
|dateTime||=, !=, <, >, <=, >=, is null, is not null||Datetime with default (Pacific) time zone: |
Date with default (Pacific) time zone:
Datetime with Eastern Standard Time:
Date with GMT:
|Use the following format for dateTime values (ISO 8601): |
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:
To specify only a date, omit the time:
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|| ||Quotes are optional around this value.|
|string||=, !=, >, <, >=, <=, is null, is not null|| ||Strings are enclosed in single quotes: |
|string||like, is null, is not null|| ||The string matches the specified query. This can be used to find strings using one of two wildcard characters: the percent 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
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.
Export ZOQL supports the filter relationship operators
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
select AccountNumber from account where AutoPay = true and Status='Active'
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
Active (or both).
select AccountNumber from account where Status='Draft' or Status='Active'
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'
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'))