Export ZOQL Select Statements

Overview

This topic provides usage information about the Export ZOQL select statement.

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

Query Examples

To query for all accounts that have active subscriptions that pay with a Visa credit card, issue the following query:

SELECT Subscription.Name, Account.Name FROM Subscription WHERE Subscription.Status='Active' AND DefaultPaymentMethod.CreditCardType='Visa'

You need not use the data source name as a qualifier for its fields. An identical query to the one above would be:

SELECT Name, Account.Name FROM Subscription WHERE Status='Active' AND DefaultPaymentMethod.CreditCardType='Visa'

Asterisk Wildcard Support

If you want to see all of the fields for a given object, you can use an asterisk in place of the field names:

SELECT * FROM Account

This query returns all of the fields of the Account object. 

SELECT AccountNumber, Balance, billtocontact.* FROM Account

SELECT Accountnumber, Balance, BillToContact.Firstname, BillToContact.Lastname, BillToContact.Homephone FROM Account

SELECT Account.accountnumber, Account.balance, Billtocontact.firstname, Billtocontact.lastname, Billtocontact.homephone FROM Account

Reserved and Escaped Characters

You must escape single (') and double quotes ("). You can escape single or double quotes by repeating them, as in SQL.

For example, to escape a single quote:

SELECT AccountNumber FROM account WHERE Name = 'San Francisco''s Finest'

Column Alias Support

You can specify an alias for a column name when you create a data source export. 

Use the following syntax to create a column alias:

select field as "name" from table;

Where field is the name of the field that you are creating an alias for, name is the alias (specified in quotes), and table is the name of the table that contains the field.