Knowledge Center

Knowledge Center > API > SOAP API > Zuora Object Query Language > Export ZOQL > Export ZOQL Clauses

Export ZOQL Clauses

Overview

Export ZOQL supports the order by, group by, and having clauses. 

Note that Export ZOQL is used for creating exports from Zuora data sources, and uses a different syntax than standard ZOQL.

Order By 

Use the order by clause to sort results in ascending (ASC) or descending (DESC) in the order by clause.

By default, the ZOQL sorts results in ascending order if you do not specify ASC or DESC in the order by clause.

You can specify multiple fields in the order by clause. 

Example

For example, the following two statements will return results in ascending order:

select * from Account order by ID asc;

select * from Account order by ID;

The following demonstrates how to use multiple fields with order by:

select id from account order by ID, name;

Group By

Use the group by clause to project rows having common values into a smaller set of rows. Use the group by clause with aggregate functions

You can specify up to five (5) fields in a group by clause. 

Example

select count(a),max(c),b from A group by b;
select A.a,avg(A.c) from A group by A.a, B.b;

Having

Use the having clause to return only rows where aggregate values meet the conditions that you specify. 

For example, you can use a having clause as a filter on a group by result. 

Example

select A.a, avg(A.b) from A where B.name is null group by A.a having max(B.c)>0 or (max(B.c) = 0 and (not (A.a >0));

Limit

Use the limit clause to restrict the results returned by a query.

Example

You can use the limit clause to return the first five rows of a query:

select * from account limit 5;

You can use the limit clause to return the 100 rows of a result after the fiftieth row. In this example, the query will return rows 50 through 149. 

select * from account limit 50,100;

Export ZOQL Clause Limits

Export ZOQL clauses have the following limits: 

Limit Description
Maximum Fields on group by clause Maximum of five fields allowed when using group by.
Maximum Conditions Allowed You can have a maximum of 200 conditions in the WHERE clause of a query. For example, you can have up to 200 distinct AND or OR clauses.

If you exceed the maximum number of conditions, the following error message is displayed:

You have exceeded the maximum number of parameters in the Where clause. 
Alias Function Limited Support The alias function is allowed only to define column labels in the result file. You cannot replace an aggregate function with its alias in having or orderby clauses. Duplicate aliases for column labels are not supported.
Nested Aggregate Functions Not Allowed Nested aggregate functions are not allowed.
Last modified
00:33, 5 Jun 2015

Tags

Classifications

(not set)