Filter Statements

From API Documentation

As described in the Zuora Object Query Language topic, a query includes filter statements. A filter statement must evaluate as true, or the item won't be included in the query results. Filter statements can use different operators, which are described in the next section.

Operators

The supported operators are explained in the following table. When the conditions are met, the expression evaluates as true and the item is included in the query results. For more information on the field types, see Field Types.

Operator	Supported Data Types	Conditions
=	booleans, dateTimes, numbers, and strings Items on both sides of this operator must be exactly equal to each other.	Items on both sides of this operator must be equal to each other.
!=	booleans, dateTimes, numbers, and strings	Items on both sides of this operator cannot be equal to each other.
>=	dateTimes, numbers, and strings	The item to the left of this operator must be greater than or equal to the item on the right of this operator.
<=	dateTimes, numbers, and strings	The item to the left of this operator must be less than or equal to the item on the right of this operator.
like	strings	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 following query will return account numbers for all accounts where the value in the Name field starts with the letter Z followed by any characters. select AccountNumber from account where Name like 'Z%'
and	strings	This is a logical operator that requires both specified items to be true. For example, in the following statement, IDs will only be returned for accounts where AutoPay is set to true and Status is Active. select AccountNumber from account where AutoPay = true and Status='Active'
or	strings	This is a logical operator that requires either one or the other of a specified item to be true. (Both can also be true.) For example, in the following statement, IDs will be returned for all accounts whose status is either Draft or Active (or both). select AccountNumber from account where Status='Draft' or Status='Active'
not	strings	This is a logical operator that requires the specified item to be false. For example, the following statement returns information from all accounts with the name "Eve," a variation that includes "Adam," and that were not created on January 1, 2010: select * from Account where name = 'Eve' or name like 'Adam%' or not createdDate = '2010-01-01' Note: Support for NOT was added in version 30 of the Z-Commerce API.

The following table shows which operators are supported for the different data types:

Data Type	Supported Operators	Example	Notes
boolean	=, !=	select AccountNumber from account where AutoPay != true	Use true or false. Don't use quotation marks around the desired value.
dateTime	=, !=, >=, <=	select AccountNumber from account where CreatedDate <= 2008-09-12T05:07:32	Use the following format for dateTime values, where T indicates time: YYYY-MM-DDThh:mm:ss If a date or time is a single-digit number, supply a leading zero (0) as shown in the example.
number	=, !=, >=, <=	select AccountNumber from account where BillCycleDay >= 15
string	=, !=, >=, <=, like, and, or, not	select AccountNumber from account where Status = 'Draft'	Strings are enclosed in single quotes ('like this').

Use of Null

You can use null as a value in a filter statement; for example, to find all accounts with a purchase order number, you might use the following statement:

select AccountNumber from account where PurchaseOrderNumber != null

Reserved and Escaped Characters

You cannot use either the single quotation mark (') or the backslash character (\) as string values in your queries without using a special character to indicate that they are part of your search and not a command or the end of a string. If you need to use them, you must place a backslash before them. (They are then called escaped characters.)

For example, in the following statement, where there is an apostrophe in the name, a backslash has been used to indicate that this is part of the search and not the end of the string.

select AccountNumber from account where Name = 'San Francisco\'s Finest'

In addition, you can use certain escaped characters as follows:

Escaped Character Sequence	Meaning
\b	Bell
\f	Form feed
\n	New line
\r	Carriage return
\t	Tab
\'	A single quotation mark (as a character)
\"	A double quotation mark (as a single character)
\\	A backslash