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.
|Data Type||Supported Operators||Example||Notes|
|boolean||=, !=, is null, is not null||
|dateTime||=, !=, <, >, <=, >=, is null, is not null||Datetime with default time zone:
Date with default 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.
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:
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 the default time zone, specify it in the statement.
See Simplified Dates for more information about using dates with Export ZOQL.
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.
|number||=, !=, >, <, >=, <=, is null, is not null||
||Quotes are optional around this value.|
|string||=, !=, >, <, >=, <=, is null, is not null||
Strings are enclosed in single quotes:
Strings are compared lexicographically.
If you compare a null value field with a non-null string using the
|string||like, is null, is not null||
Account name that starts with
Account name that contains four characters and ends with
|The string matches the specified query. This can be used to find strings using one of two wildcard characters: the percent character,
The query in the first example returns account numbers for all accounts where the value in the Name field starts with the letter
The query in the second example returns account numbers for all accounts where the value in the Name field is composed of four characters and ends with the string
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 Relationship Operator
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
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
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'))
Comparing fields in filter statements
You can compare Zuora fields using binary operators in the filter statements. For example:
select Account.Id, Invoice.Id from Invoice where Invoice.Balance >= Account.Balance
select Id from Subscription where ContractEffectiveDate != ServiceActivationDate
select Id from Subscription where ServiceActivationDate = DateCustomField__c
You can compare the DateTime Field with the Date Field. For example:
select Id from Subscription where CreatedDate = DateCustomField__c
This query will find all the records created within the entire day of DateCustomField__c.
For more information, see Special date/time features in Export ZOQL.