Export ZOQL Filter Statements

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'`	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. 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	`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'` Strings are compared lexicographically. If you compare a null value field with a non-null string using the `!=` operator, the comparison does not return true. For example, the result of the following query does not include the records where `discountlevel` is `null`: SELECT id, discountlevel FROM productrateplancharge WHERE discountlevel != `rateplan`
string	like, is null, is not null	Account name that starts with `Z`: `select AccountNumber from account where Name like 'Z%'` Account name that contains four characters and ends with `abc`: `select AccountNumber from account where Name like '_abc'`	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. Note that the percent character can be used only once in each query, either at the beginning or end of the pattern. The query in the first example returns account numbers for all accounts where the value in the Name field starts with the letter `Z` followed by any characters. For example, `Zuora` or `Zu`. 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 `abc`. For example, `aabc` or `Sabc`, but not `abcd` or `SEabc`.

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'))

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.