Supported SQL syntax

SHOW TABLES statement

SHOW TABLES

Table
account
accountingcode
accountingperiod
amendment
billingrun
...

You can find all the available tables in the "Available Tables" section below. Once you know the tables that you can query from, check out the available fields.

SHOW COLUMNS and DESCRIBE Statements

The following statements retrieve metadata about tables in your Zuora tenant:

• SHOW COLUMNS FROM|IN {table}
• DESCRIBE {table}
• DESC {table}

The different statements are functionally equivalent.

SHOW COLUMNS FROM Account

Column,Type,Extra,Comment
accountnumber,varchar,,AccountNumber
additionalemailaddresses,varchar,,AdditionalEmailAddresses
allowinvoiceedit,boolean,,AllowInvoiceEdit
autopay,boolean,,AutoPay
baddebt__c,varchar,,BadDebt__c
balance,"decimal(18,6)",,Balance

The possible column types are:

• bigint
• boolean
• date
• decimal(18,6)
• timestamp with time zone
• varchar

To learn more about the available tables, see the "Available Tables" section below.

SHOW FUNCTIONS statement

SHOW FUNCTIONS

Function,Return Type,Argument Types,Function Type,Deterministic,Description
...
min,E,E,aggregate,true,Returns the minimum value of the argument
min,array(E),"E, bigint",aggregate,true,Returns the minimum values of the argument
min_by,V,"V, K",aggregate,true,"Returns the value of the first argument, associated with the minimum value of the second argument"
min_by,array(V),"V, K, bigint",aggregate,true,Returns the values of the first argument associated with the minimum values of the second argument
minute,bigint,interval day to second,scalar,true,minute of the hour of the given interval
minute,bigint,time,scalar,true,minute of the hour of the given time
minute,bigint,time with time zone,scalar,true,minute of the hour of the given time
minute,bigint,timestamp,scalar,true,minute of the hour of the given timestamp
minute,bigint,timestamp with time zone,scalar,true,minute of the hour of the given timestamp
...

To maximize the utility of Data Query, Zuora has made a large number of functions available for use in ​ SELECT ​ statements. For these functions to complete successfully, their usage must conform to the limitations described in ​ Limitations ​ ​.

Accessing Billing and Revenue objects in Data Query

Depending on where you access Data Query, the way you reference Billing and Revenue objects differs slightly:

• In Zuora Billing UI (Platform Data Query)

  • Use Billing, Payments, and Platform objects as is.

  • Use Revenue objects with the prefix revenuelive.<rev_object_name>

• In Zuora Revenue UI (Revenue Data Query)

  • Use Revenue objects as is.

  • Use Billing, Payments, and Platform objects with the prefix live.<billing_object_name>

These prefixes help Data Query identify whether an object belongs to the Billing (Platform) or Revenue data model when both are available in the same environment.

SELECT statements

You can use SELECT statements to retrieve object data from your Zuora tenant.

Data Query supports SQL-92 syntax for SELECT statements, including (but not limited to) joins, subqueries, unions, groupings, and orderings. Data Query also supports operators and functions such as min, max, avg, count, year, and so on. See the "Examples" section below for some sample queries.

Select data from one table

SELECT {column name, ..., ...} FROM {table}

If the table contains more than 1,000,000 records, the query will fail because it reaches the input limit of Data Query.

If the table contains less than 1,000,000 records but more than 100,000 records, the query will fail because it reaches the output limit of Data Query. You can add a WHERE clause to narrow down the output records. See Best practices of Data Query for more information.

Select data from multiple tables

SELECT {column name, ..., ...} FROM {table name, ..., ...}

SELECT account.name, subscription.id FROM Account, Subscription

Note that the Data Query processing limitations are applicable.

For information on joining data across multiple tables, see Join data across multiple tables.

Filter deleted records using WHERE clause

Note that Data Query is subject to Zuora Data Retention Policy. The retention period of deleted data is 30 days. You can only retrieve deleted data for 30 days through Data Query.