Construct SQL Queries in Data Query

Last updated
Save as PDF

This article summarizes the supported SQL syntax in Data Query and provides sample queries.

Supported SQL Syntax

Data Query only supports SQL statements that retrieve data from your Zuora tenant. You cannot use SQL queries to create, update, or delete data in your Zuora tenant.
Data Query supports the following SQL statements:

SHOW TABLES - For retrieving a list of the available tables in your Zuora tenant
SHOW COLUMNS and DESCRIBE - For retrieving metadata about a table in your Zuora tenant
SHOW FUNCTIONS - for retrieving a complete list of the functions that you can use in SELECT statements
SELECT - Includes support for joins, subqueries, unions, groupings, orderings, and so on

Note that Data Query uses Trino version 351.

SHOW TABLES Statement

The SHOW TABLES statement retrieves a list of the available tables in your Zuora tenant. It will list all the objects in your tenant. You can use this statement to understand Zuora Data Model as it applies to your tenant.

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.

Each row in the exported data describes a column in the specified table. When constructing SQL queries, you need to know the table columns to be retrieved. Columns are also helpful when using SQL JOINs. When two tables share a column, for example, the column ID in the Account table can be found in other tables and is called AccountID, you can use JOIN to discover insights across objects.

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

To retrieve the complete list of the functions that you can use in SELECT statements, you can use the SHOW FUNCTIONS statement. You can view the list of functions in the Data Query Functions.csv file.

This file contains the list of all the functions that are currently available. However, as the list of functions is frequently updated, you can get the latest set of available functions from the application by running theSHOW FUNCTION query.

Navigate to Platform > Data Query
On the top-right, click Create New Data Query
Type SHOW FUNCTION and click Run Query. The latest list of available functions is displayed.
Click Download to download the .csv of the available functions.

For example:

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.

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}

When querying from one table, you can list column names with or without type indicators.
For example:

```
SELECT name FROM Account
```
```
SELECT account.name FROM Account
```

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, ..., ...}

When querying from one table, you must list column names with type indicators.
For example:

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

Note that the Data Query processing limitations are applicable.

Join Data Across Multiple Tables

Take the following steps to construct your query by joining data across multiple tables:

Choose the columns.
SELECT name, id, name
Choose the tables.
SELECT account.name, account.id, productrateplancharge.name
Determine which columns are used to join tables through DESCRIBE or SHOW COLUMNS statement.
Account.id = subscription.accountid Subscription.id = rateplan.subscriptionid Rateplan.id = rateplancharge.rateplanid Rateplancharge.id = productrateplancharge.rateplanchargeid
Add FROM before the first table, and then JOINs and ONs to the connecting columns in tables
FROM Account JOIN Subscription ON Account.id = subscription.accountid JOIN RatePlan ON Subscription.id = rateplan.subscriptionid JOIN ProductRatePlan ON RatePlan.productrateplanid = ProductRatePlan.id JOIN ProductRatePlanCharge ON ProductRatePlan.id = ProductRatePlanCharge.productrateplanid
Add WHERE clause to narrow down the returned records
WHERE productrateplancharge.updateddate >= timestamp '2019-01-01 -07:00'
Construct the whole SQL query
SELECT account.name, account.id, productrateplancharge.name FROM Account JOIN Subscription ON Account.id = subscription.accountid JOIN RatePlan ON Subscription.id = rateplan.subscriptionid JOIN ProductRatePlan ON RatePlan.productrateplanid = ProductRatePlan.id JOIN ProductRatePlanCharge ON ProductRatePlan.id = ProductRatePlanCharge.productrateplanid WHERE productrateplancharge.updateddate >= timestamp '2019-01-01 -07:00'

Filter Deleted Records using WHERE clause

Deleted records are not included by default in Data Query Live queries. You can always filter deleted records using WHERE clause in Data Query Live. Note that you must add deleted in the SELECT clause as a selected column if you use the deleted column in the WHERE clause. For example:

The deleted records can be specifically found by querying against the deleted column in appropriate tables with the following syntax:
```
SELECT name, deleted FROM Account, Subscription WHERE Account.deleted = TRUE
```
The deleted records can be filtered out with the following syntax:
```
SELECT name, deleted FROM Account WHERE deleted = FALSE
```

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.

Encryption

Data Query supports data encryption with custom keys through Data Query API.

To encrypt your query result:

Generate an RSA key pair locally and keep your key pair safe. Note that Data Query only supports 1024-bit RSA keys.

Submit data query with encryptionKey field set to your base64 encoded public key in the request body. See the following example:

{
  "query": "select * from account",
  "outputFormat": "CSV",
  "compression": "GZIP",
  "encryptionKey": "MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCHRHXrqriUa5nkXP6tvCv3uQtH5fYezHFZ8kXeK2LWTmY6i7FGzRcDlDpuY72iLTPTqbYKyky1EhTlr/UaMn1tugb+wVbWJ81AnisAxCDmnkbMwkfqPHxNlu+Tg2OXtgFd4QCENAgHt/DOqD9HQV+5KHp59DdoD2BzpNf63b3D1QIDAQAB",
  "output" : {
    "target": "S3"
  }
}

Once the query is completed, the output data file is encrypted. You must use the corresponding private key in your key pair to decrypt the query results.

Available Tables

Depending on the available features in each tenant, the available tables in Data Query might be different between tenants. You can use the SHOW TABLES statement to retrieve a complete and exact list of the available tables. You can also use Data Query Schema Browser to view the full list of all available tables.

Each table corresponds to a Zuora object type, and most tables correspond to the base object of a Zuora data source. To learn about particular object types, you can refer to the data source documentation. You can also use the SOAP API documentation to learn about some object types.

The main difference between tables in Data Query and data sources is that tables in Data Query are not pre-joined to other tables. When you query a table in Data Query, you can arbitrarily join other tables to retrieve any data that you require. In the corresponding data source, Zuora has pre-joined the base object to a fixed set of related object types; you cannot retrieve data from other object types.

To learn how different object types relate to one another, see:

To get a complete and exact list of the available tables in your tenant, use SHOW TABLES. See the detailed information of the following tables in Data Query. For Audit Trail tables, see Data Model of Audit Trail.

Table	Each Row Represents	Columns
`Account`	A customer account	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Account in the SOAP API documentation
`AccountingPeriod` Only available if you use Zuora Finance	An accounting period	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see AccountingPeriod in the SOAP API documentation
`AccountingCode`	An accounting code	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see AccountingCode in the SOAP API documentation
`Amendment` Not available if the Orders feature is enabled	An amendment to a subscription	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Amendment in the SOAP API documentation
`ApplicationGroup` Only available if the Invoice Settlement feature is enabled	A group of payment, refund, and credit memo applications	Use `SHOW COLUMNS` to list the available columns
`BillingRun`	A billing run	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see BillRun in the SOAP API documentation
`Collections_ApmPaymentRuns`	A payment run initiated in the Advanced Payment Manager feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_CollectionsWindowInfo`	An account that is in collection based on the system condition and the conditions you defined in the Collections Window feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_ConnectorExecutions`	A processing record of the configured lockbox file format in the Configurable Lockbox feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_LockboxRecords`	A file record of the configured lockbox file format in the Configurable Lockbox feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_Lockboxes`	A lockbox file format configured in the Configurable Lockbox feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_LockboxPayment`	A payment record that has been completed or is in progress in the Configurable Lockbox feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_RetryAttempts`	A payment retry attempt initiated in the Configurable Payment Retry feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_CustomerGroups`	A customer group configured in the Configurable Payment Retry feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_MetricSnapshots`	A record of the retry metics in the Configurable Payment Retry feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_RetryCycles`	A retry cycle in the Configurable Payment Retry feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_Groups`	A user group in the Notes feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_Note`	A note in the Notes feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_Replies`	A reply in the Notes feature	Use `SHOW COLUMNS` to list the available columns.
`Collections_Commentables`	A user in the Notes feature	Use `SHOW COLUMNS` to list the available columns.
`Contact`	A contact person. For example, the Bill To contact of a customer account	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Contact in the SOAP API documentation
`ContactSnapshot`	A copy of the Bill To or Sold To contact information from a posted invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see ContactSnapshot in the SOAP API documentation
`Country`	A country or region	Use `SHOW COLUMNS` to list the available columns. For more information, see Manage countries, regions, and subregions.
`ChargeMetrics`	A charge metrics service for accessing the key metrics for Rate Plan Charges in Zuora.	Use `SHOW COLUMNS` to list the available columns.
`ChargeMetricsDiscountAllocationDetail`	The discount allocation detail in the charge metrics	Use `SHOW COLUMNS` to list the available columns.
`CreditBalanceAdjustment` Deprecated if the Invoice Settlement feature is enabled	An adjustment to the credit balance of a customer account	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see CreditBalanceAdjustment in the SOAP API documentation
`CreditMemo` Only available if the Invoice Settlement feature is enabled	A credit memo that is owned by a customer account	Use `SHOW COLUMNS` to list the available columns
`CreditMemoApplication` Only available if the Invoice Settlement feature is enabled	A credit memo that is applied to an invoice or a debit memo	Use `SHOW COLUMNS` to list the available columns
`CreditMemoApplicationItem` Only available if the Invoice Settlement feature is enabled	A credit memo that is applied to a line item in an invoice or a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`CreditMemoItem` Only available if the Invoice Settlement feature is enabled	A line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`CreditMemoPart` Only available if the Invoice Settlement feature is enabled	An applied or unapplied portion of a credit memo	Use `SHOW COLUMNS` to list the available columns
`CreditMemoPartItem` Only available if the Invoice Settlement feature is enabled	A portion of a credit memo that is applied to a line item in an invoice or a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`CreditTaxationItem` Only available if the Invoice Settlement feature is enabled	A taxation line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`DebitMemo` Only available if the Invoice Settlement feature is enabled	A debit memo that is owned by a customer account	Use `SHOW COLUMNS` to list the available columns
`DebitMemoItem` Only available if the Invoice Settlement feature is enabled	A line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`DebitTaxationItem` Only available if the Invoice Settlement feature is enabled	A taxation line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`DiscountAppliedMetrics`	A discount rate plan charge that is applied to another rate plan charge	Use `SHOW COLUMNS` to list the available columns
`FXCustomRate` Only available if the Custom Foreign Currency Exchange Rates feature is enabled	A custom exchange rate	Use `SHOW COLUMNS` to list the available columns
`Invoice`	An invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Invoice in the SOAP API documentation
`InvoiceAdjustment` Deprecated	An adjustment to an invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see InvoiceAdjustment in the SOAP API documentation
`InvoiceItem`	A line item in an invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see InvoiceItem in the SOAP API documentation
`InvoiceItemAdjustment` Deprecated if the Invoice Settlement feature is enabled	An adjustment to a line item in an invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see InvoiceItemAdjustment in the SOAP API documentation
`InvoicePayment` Deprecated if the Invoice Settlement feature is enabled	A payment that is applied to an invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see InvoicePayment in the SOAP API documentation
`JournalEntry` Only available if you use Zuora Finance	A journal entry	Use `SHOW COLUMNS` to list the available columns
`JournalEntryItem` Only available if you use Zuora Finance	A line item in a journal entry	Use `SHOW COLUMNS` to list the available columns.
`JournalRun` Only available if you use Zuora Finance	A journal run	Use `SHOW COLUMNS` to list the available columns.
`notificationhistorycallout`	A callout notification history	Use `SHOW COLUMNS` to list the available columns.
`notificationhistoryemail`	An email notification history	Use `SHOW COLUMNS` to list the available columns.
`notificationhistoryemailevent`	An email-sending event, such as Bounced events, or Delivered events. Note that you can find events of a particular email history in this table only if this email was sent from the default email server or Advanced SMTP server. For more information about Zuora SMTP server, see Configure an SMTP server for email notifications.	Use `SHOW COLUMNS` to list the available columns.
`OrderAction` Only available if the Order Metrics feature, the Orders feature, or the Orders Harmonization feature is enabled	An order action that is applied to a subscription	Use `SHOW COLUMNS` to list the available columns.
`OrderActionRatePlan` Only available if the Orders feature or the Orders Harmonization feature is enabled. Click the use case below to see a query sample. Query the rate plan added by the Add Product order action in an order `SELECT rp.Id, rp.Name, rp.ProductRatePlanId, rp.SubscriptionId, rp.CreatedDate FROM Orders o JOIN OrderAction oa ON oa.OrderId = o.Id JOIN OrderActionRatePlan oaRp ON oaRp.OrderActionId = oa.Id JOIN RatePlan rp ON rp.Id = oaRp.RatePlanId WHERE o.OrderNumber = 'O-00001' AND oa.Type = 'AddProduct'`	A rate plan (in a subscription) that has been created or amended by a specific order action	Use SHOW COLUMNS to list the available columns.
`OrderLineItem` The Order Line Items feature is now generally available. You need to enable the Orders or Orders Harmonization feature to access the Order Line Items feature.	An order line item	Use SHOW COLUMNS to list the available columns.
`OrderElp` Only available if the Order Metrics feature or the Orders feature is enabled The following Order Metrics have been deprecated. Any new customers who onboard on Orders or Orders Harmonization will not get these metrics. The Order ELP and Order Item objects The "Generated Reason" and "Order Item ID" fields in the Order MRR, Order TCB, Order TCV, and Order Quantity objects Existing Orders customers who have these metrics will continue to be supported.	An "extended list price" metric for an order action	Use `SHOW COLUMNS` to list the available columns
`OrderItem` Only available if the Order Metrics feature or the Orders feature is enabled The following Order Metrics have been deprecated. Any new customers who onboard on Orders or Orders Harmonization will not get these metrics. The Order ELP and Order Item objects The "Generated Reason" and "Order Item ID" fields in the Order MRR, Order TCB, Order TCV, and Order Quantity objects Existing Orders customers who have these metrics will continue to be supported.	An order item	Use `SHOW COLUMNS` to list the available columns
`OrderMrr` Only available if the Order Metrics feature, the Orders feature, or the Orders Harmonization feature is enabled The following Order Metrics have been deprecated. Any new customers who onboard on Orders or Orders Harmonization will not get these metrics. The Order ELP and Order Item objects The "Generated Reason" and "Order Item ID" fields in the Order MRR, Order TCB, Order TCV, and Order Quantity objects Existing Orders customers who have these metrics will continue to be supported.	A "monthly recurring revenue" metric for an order action	Use `SHOW COLUMNS` to list the available columns
`OrderQuantity` Only available if the Order Metrics feature, the Orders feature, or the Orders Harmonization feature is enabled The following Order Metrics have been deprecated. Any new customers who onboard on Orders or Orders Harmonization will not get these metrics. The Order ELP and Order Item objects The "Generated Reason" and "Order Item ID" fields in the Order MRR, Order TCB, Order TCV, and Order Quantity objects Existing Orders customers who have these metrics will continue to be supported.	A "quantity" metric for an order action	Use `SHOW COLUMNS` to list the available columns
`Orders` Only available if the Order Metrics feature, the Orders feature, or the Orders Harmonization feature is enabled	An order	Use `SHOW COLUMNS` to list the available columns
`OrderTcb` Only available if the Order Metrics feature, the Orders feature, or the Orders Harmonization feature is enabled The following Order Metrics have been deprecated. Any new customers who onboard on Orders or Orders Harmonization will not get these metrics. The Order ELP and Order Item objects The "Generated Reason" and "Order Item ID" fields in the Order MRR, Order TCB, Order TCV, and Order Quantity objects Existing Orders customers who have these metrics will continue to be supported.	A "total contracted billing" metric for an order action	Use `SHOW COLUMNS` to list the available columns
`OrderTcv` Only available if the Order Metrics feature, the Orders feature, or the Orders Harmonization feature is enabled The following Order Metrics have been deprecated. Any new customers who onboard on Orders or Orders Harmonization will not get these metrics. The Order ELP and Order Item objects The "Generated Reason" and "Order Item ID" fields in the Order MRR, Order TCB, Order TCV, and Order Quantity objects Existing Orders customers who have these metrics will continue to be supported.	A "total contract value" metric for an order action	Use `SHOW COLUMNS` to list the available columns
`Payment`	A payment	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Payment in the SOAP API documentation
`PaymentApplication` Only available if the Invoice Settlement feature is enabled	A payment that is applied to an invoice or a debit memo	Use `SHOW COLUMNS` to list the available columns
`PaymentApplicationItem` Only available if the Invoice Settlement feature is enabled	A payment that is applied to a line item in an invoice or a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`PaymentGatewayReconciliation EventLog`	An event that was processed by a payment reconciliation job	Use `SHOW COLUMNS` to list the available columns
`PaymentMethod`	A payment method	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see PaymentMethod in the SOAP API documentation
`PaymentMethodSnapshot`	A copy of the payment method that was used in a transaction	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see PaymentMethodSnapshot in the SOAP API documentation
`PaymentMethodTransactionLog`	A transaction from Zuora to the payment gateway associated with a payment method. If Zuora Fraud Protection is enabled, the record of data related to the fraud protection service is also included.	Use `SHOW COLUMNS` to list the available columns
`PaymentPart` Only available if the Invoice Settlement feature is enabled	An applied or unapplied portion of a payment	Use `SHOW COLUMNS` to list the available columns
`PaymentPartItem` Only available if both Invoice Settlement and Invoice Item Settlement are enabled	A portion of a payment that is applied to a line item in an invoice or a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`PaymentReconciliationJob`	A payment reconciliation job	Use `SHOW COLUMNS` to list the available columns
`PaymentReconciliationLog`	A reconciled transaction that was processed by a payment reconciliation job	Use `SHOW COLUMNS` to list the available columns
`PaymentRun`	A payment run	Use `SHOW COLUMNS` to list the available columns
`PaymentTransactionLog`	A transaction from Zuora to the payment gateway associated with a payment. If Zuora Fraud Protection is enabled, the record of data related to the fraud protection service is also included.	Use `SHOW COLUMNS` to list the available columns
`ProcessedUsage`	Uploaded usage that has been processed	Use `SHOW COLUMNS` to list the available columns
`Product`	A product in your product catalog	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Product in the SOAP API documentation
`ProductFeature` Only available if you use Entitlements	A feature in your product catalog	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see ProductFeature in the SOAP API documentation
`ProductRatePlan`	A rate plan in your product catalog	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see ProductRatePlan in the SOAP API documentation
`ProductRatePlanCharge`	A charge in your product catalog	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see ProductRatePlanCharge in the SOAP API documentation
`ProductRatePlanChargeTier`	A charge tier in your product catalog	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see ProductRatePlanChargeTier in the SOAP API documentation
`RatePlan`	A rate plan in a subscription	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see RatePlan in the SOAP API documentation
`RatePlanCharge`	A charge in a subscription	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see RatePlanCharge in the SOAP API documentation
`RatePlanChargeTier`	A charge tier in a subscription	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see RatePlanChargeTier in the SOAP API documentation
`Ramp`	A ramp in an order defined in a ramp deal	Use `SHOW COLUMNS` to list the available columns.
`RampInterval`	A ramp interval in a ramp	Use `SHOW COLUMNS` to list the available columns.
`Refund`	A refund	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Refund in the SOAP API documentation
`RefundApplication` Only available if the Invoice Settlement feature is enabled	A refund that is applied to a payment or a credit memo	Use `SHOW COLUMNS` to list the available columns
`RefundApplicationItem` Only available if the Invoice Settlement feature is enabled	a refund that is applied to a line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`RefundInvoicePayment` Deprecated if the Invoice Settlement feature is enabled	A refunded portion of a payment that is applied to an invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see RefundInvoicePayment in the SOAP API documentation
`RefundPart` Only available if the Invoice Settlement feature is enabled	An applied or unapplied portion of a refund	Use `SHOW COLUMNS` to list the available columns
`RefundPartItem` Only available if both Invoice Settlement and Invoice Item Settlement are enabled	A portion of a refund that is applied to a line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`RefundTransactionLog`	A transaction from Zuora to the payment gateway associated with a payment	Use `SHOW COLUMNS` to list the available columns
`RevenueChargeSummary` Only available if you use Zuora Finance - Revenue	A revenue distribution summary of a charge	Use `SHOW COLUMNS` to list the available columns
`RevenueChargeSummaryItem` Only available if you use Zuora Finance - Revenue	An item in a revenue distribution summary of a charge	Use `SHOW COLUMNS` to list the available columns
`RevenueEvent` Only available if you use Zuora Finance - Revenue	A revenue event	Use `SHOW COLUMNS` to list the available columns
`RevenueEventCreditMemoItem` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue event that is associated with a line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueEventDebitMemoItem` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue event that is associated with a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueEventInvoiceItem` Only available if you use Zuora Finance - Revenue	A revenue event that is associated with a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueEventInvoiceItem Adjustment` Only available if you use Zuora Finance - Revenue	A revenue event that is associated with an adjustment to a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueEventItem` Only available if you use Zuora Finance - Revenue	A revenue event item	Use `SHOW COLUMNS` to list the available columns
`RevenueEventItemCreditMemo Item` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue event item that is associated with a line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueEventItemDebitMemo Item` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue event item that is associated with a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueEventItemInvoice Item` Only available if you use Zuora Finance - Revenue	A revenue event item that is associated with a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueEventItemInvoice ItemAdjustment` Only available if you use Zuora Finance - Revenue	A revenue event item that is associated with an adjustment to a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueEventType` Only available if you use Zuora Finance - Revenue	A revenue event type	Use `SHOW COLUMNS` to list the available columns
`RevenueSchedule` Only available if you use Zuora Finance - Revenue	A revenue schedule	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleCreditMemo Item` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue schedule that is associated with a line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleDebitMemo Item` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue schedule that is associated with a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleInvoice Item` Only available if you use Zuora Finance - Revenue	A revenue schedule that is associated with a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleInvoice ItemAdjustment` Only available if you use Zuora Finance - Revenue	A revenue schedule that is associated with an adjustment to a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleItem` Only available if you use Zuora Finance - Revenue	a revenue schedule item	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleItemCreditMemo Item` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue schedule item that is associated with a line item in a credit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleItemDebitMemo Item` Only available if you use Zuora Finance - Revenue and the Invoice Settlement feature is enabled	A revenue schedule item that is associated with a line item in a debit memo	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleItemInvoice Item` Only available if you use Zuora Finance - Revenue	a revenue schedule item that is associated with a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`RevenueScheduleItemInvoice ItemAdjustment` Only available if you use Zuora Finance - Revenue	A revenue schedule item that is associated with an adjustment to a line item in an invoice	Use `SHOW COLUMNS` to list the available columns
`State`	A subregion of a country or region	Use `SHOW COLUMNS` to list the available columns. For more information, see Manage countries, regions, and subregions.
`Subscription`	A subscription	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see Subscription in the SOAP API documentation
`SubscriptionStatusHistory`	The status history of a subscription	Use `SHOW COLUMNS` to list the available columns
`TaxableItemSnapshot` Only available if the Taxable Item Snapshot feature is enabled	A copy of information that was used in a tax calculation	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see TaxableItemSnapshot in the SOAP API documentation
`TaxationItem`	A taxation line item in an invoice	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions, see TaxationItem in the SOAP API documentation
`UpdaterBatch`	A batch of payment methods that was sent to a payment method updater service provider	Use `SHOW COLUMNS` to list the available columns
`UpdaterDetail`	A payment method update	Use `SHOW COLUMNS` to list the available columns
`Usage`	A usage record If the Active Rating feature is enabled, Each row represents a usage record that was created via the Active Rating feature	Use `SHOW COLUMNS` to list the available columns. For relevant descriptions if the Active Rating feature is not enabled, see Usage in the SOAP API documentation
`User` Only platform admin users can access the `User` table	A user in your Zuora tenant	`email` `firstname` `id` `lastname` `username`
`workflow`	A workflow version or a workflow run. For more information, see Use Data Query to report on Workflow.	Use `SHOW COLUMNS` to list the available columns
`workflow_api_call`	An API call made during a workflow run. For more information, see Use Data Query to report on Workflow.	Use `SHOW COLUMNS` to list the available columns
`workflow_linkage`	A connection between two tasks in a workflow version. For more information, see Use Data Query to report on Workflow.	Use `SHOW COLUMNS` to list the available columns
`workflow_task`	A task that is part of a workflow version or a workflow run. For more information, see Use Data Query to report on Workflow.	Use `SHOW COLUMNS` to list the available columns
`workflow_task_usage`	Your entity's usage of Workflow over a single day. For more information, see Use Data Query to report on Workflow.	Use `SHOW COLUMNS` to list the available columns
`workflow_definition`	A workflow definition that has at least one workflow version. For more information, see Use Data Query to report on Workflow.	Use `SHOW COLUMNS` to list the available columns
`attachment`	The information of a file attached to a Zuora object. You can now query the attachment file information of the following Zuora objects: `debitmemo` `creditmemo` `account` `subscription` `invoice`	Use `SHOW COLUMNS` to list the available columns

By default, all UUIDs are handled as 32-character strings. To enable 36-character UUIDs, submit a request at Zuora Global Support.

To assist with converting between 32-character UUIDs and 36-character UUIDs, Zuora provides the following custom functions:

Function Description

Function	Description
`to_uuid32`	Converts a 36-character object identifier to a 32-character object identifier. Newer Zuora features such as Data Query and the Active Rating feature use 36-character object identifiers, while older features use 32-character object identifiers. For example: to_uuid32('2c92c0f9-67bb-659c-0167-be58d878735f') Returns: 2c92c0f967bb659c0167be58d878735f See the "Examples" section below for a sample query that uses the `to_uuid32` function.
`to_uuid36`	Converts a 32-character object identifier to a 36-character object identifier. Newer Zuora features such as Data Query and the Active Rating feature use 36-character object identifiers, while older features use 32-character object identifiers. For example: to_uuid36('2c92c0f967bb659c0167be58d878735f') Returns: 2c92c0f9-67bb-659c-0167-be58d878735f See the "Examples" section below for a sample query that uses the `to_uuid36` function.

to_uuid32

Converts a 36-character object identifier to a 32-character object identifier. Newer Zuora features such as Data Query and the Active Rating feature use 36-character object identifiers, while older features use 32-character object identifiers.

For example:

to_uuid32('2c92c0f9-67bb-659c-0167-be58d878735f')

Returns:

2c92c0f967bb659c0167be58d878735f

See the "Examples" section below for a sample query that uses the to_uuid32 function.

to_uuid36

Converts a 32-character object identifier to a 36-character object identifier. Newer Zuora features such as Data Query and the Active Rating feature use 36-character object identifiers, while older features use 32-character object identifiers.

For example:

to_uuid36('2c92c0f967bb659c0167be58d878735f')

Returns:

2c92c0f9-67bb-659c-0167-be58d878735f

See the "Examples" section below for a sample query that uses the to_uuid36 function.

Get the date value of a timestamp

You can get the date value of a timestamp by calling the date function. The syntax of this function is as follows:

date(<timestamp>)
date(<timestamp> AT TIME ZONE <timezone>)

The date function takes the timestamp parameter and returns a date value. The timestamp parameter is required. You can get the date in a particular time zone by specifying a timezone after the timestamp.

Assuming the current date and time are 2023-01-12 04:30:00 UTC, the results of some typical examples are as follows:

Function expression	Result	Description
date(CURRENT_TIMESTAMP)	2023-01-12	Returns the date of current UTC.
date(CURRENT_TIMESTAMP AT TIME ZONE '-08:00')	2023-01-11	Returns the date of current UTC in UTC-08:00 time zone.
date(CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')	2023-01-11	Returns the date of current UTC in Los Angeles time zone (UTC-07:00 or UTC-08:00 depending on the time of the year).
date(timestamp '2022-12-30 13:30:00')	2022-12-30	Returns the date of 2022-12-30 13:30:00 UTC.
date(timestamp '2022-12-30 13:30:00 -08:00')	2022-12-30	Returns the date of 2022-12-30 13:30:00 UTC-08:00.
date(timestamp '2022-12-30 13:30:00 -08:00' AT TIME ZONE 'UTC')	2022-12-30	Returns the date of 2022-12-30 13:30:00 UTC-08:00 in UTC time zone.
date(timestamp '2022-12-30 13:30:00 -08:00' AT TIME ZONE '+08:00')	2022-12-31	Returns the date of 2022-12-30 13:30:00 UTC-08:00 in UTC+08:00 time zone.

For more information about how to use the date function in queries, see Best-practice samples for common queries.