SQL Queries in Zuora Link

Knowledge Center > API > Zuora Link > SQL Queries in Zuora Link

SQL Queries in Zuora Link

Zuora Link is in Limited Availability. If you wish to have access to the feature, submit a request at Zuora Global Support.

This article summarizes the supported SQL syntax in Zuora Link and provides sample queries.

Supported SQL Syntax

Zuora Link 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.

Zuora Link supports the following SQL statements:

  • SELECT - Includes support for joins, subqueries, unions, groupings, orderings, and so on
  • SHOW TABLES - For retrieving a list of the available tables in your Zuora tenant
  • DESCRIBE - For retrieving metadata about a table in your Zuora tenant

SELECT Statements

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

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

In addition, Zuora Link supports the following Zuora-specific functions:

Function Description

to_uuid32

Converts a 36-character object identifier to a 32-character object identifier. Newer Zuora features such as Zuora Link 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 Zuora Link 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.

SHOW TABLES Statement

The SHOW TABLES statement retrieves a list of the available tables in your Zuora tenant.

Each row in the exported data corresponds to a table that you can reference in SELECT statements. For example:

SHOW TABLES
Table
account
accountingcode
accountingperiod
amendment
billingrun
...

For a description of each table, see the "Available Tables" section below.

DESCRIBE Statements

The DESCRIBE {table} statement retrieves metadata about a table in your Zuora tenant.

Each row in the exported data describes a column in the specified table. For example:

DESCRIBE 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.

Examples 

  • For customer account A00000977, retrieve:

    • The account number
    • The account name
    • The account status
    • The username of the Zuora user who created the account
    SELECT a.accountnumber, a.name, a.status, u.username
    FROM Account a
    JOIN User u ON a.createdbyid = u.id
    WHERE a.accountnumber = 'A00000977'
    
    accountnumber,name,status,username
    A00000977,West Corporation,Active,ksmith@exmaple.com
    
  • Retrieve all processed usage records, including the account number and account name associated with each usage record:

    SELECT pu.id, pu.amount, a.accountnumber, a.name
    FROM ProcessedUsage pu
    JOIN Usage u ON pu.usageid = u.id
    JOIN Account a ON u.accountnumber = a.accountnumber
    
    id,amount,accountnumber,name
    2c92c0f8-6329-99a2-0163-52a457cf2b44,345.400000,A00000977,West Corporation
    2c92c0f8-6329-99a2-0163-52a457cf2b44,147.580000,A00000464,East Enterprises
    2c92c0f8-6329-99a2-0163-52a457cf2b44,197.190000,A00000985,North Services
    ...
    
  • Retrieve the parent account number of each customer account:

    SELECT a.accountnumber, pa.accountnumber as parentnumber
    FROM Account a
    LEFT OUTER JOIN Account pa ON a.parentid = pa.id
    
    accountnumber,parentnumber
    A00000674,A00000550
    A00000650,
    A00000783,A00000886
    ...
    
  • Retrieve all Order MRR metrics and Order TCB metrics:

    SELECT 'MRR' as metric, OrderMrr.id, OrderMrr.value
    FROM OrderMrr
    UNION
    SELECT 'TCB' as metric, OrderTcb.id, OrderTcb.value
    FROM OrderTcb
    
    metric,id,value
    MRR,2c92c0f8-6329-99a2-0163-8792e2ae736a,148.500000
    MRR,2c92c0f8-6329-99a2-0163-805c8fb924be,350.000000
    ...
    TCB,2c92c0f8-6329-99a2-0163-9a10f930a800,147.300000
    TCB,2c92c0f8-6329-99a2-0163-8230c94c7c0b,-179.990000
    ...
    
  • Retrieve the 3 products that became effective most recently:

    SELECT p.id, p.name, p.effectivestartdate
    FROM Product p
    ORDER BY p.effectivestartdate DESC
    LIMIT 3
    
    id,name,effectivestartdate
    2c92c0f8-6680-fd09-0166-a424179630c0,Spring 19 Plan,2019-03-01
    2c92c0f8-6680-fd09-0166-8187039d72f7,Student Plan,2018-07-01
    2c92c0f8-6680-fd09-0166-75fc4e54a574,Spring 18 Plan,2018-03-01
    
  • Retrieve the 3 products that became effective most recently and are still effective:

    SELECT p.id, p.name, p.effectivestartdate, p.effectiveenddate
    FROM Product p
    WHERE current_date <= p.effectiveenddate
    ORDER BY p.effectivestartdate DESC
    LIMIT 3
    
    id,name,effectivestartdate,effectiveenddate
    2c92c0f8-6680-fd09-0166-a424179630c0,Spring 19 Plan,2019-03-01,2019-05-31
    2c92c0f8-6680-fd09-0166-8187039d72f7,Student Plan,2018-07-01,2023-06-30
    2c92c0f8-6680-fd09-0166-07b3580a31d7,Full Access Plan,2017-01-01,2026-12-31
    
  • Retrieve the rate plans in a product, using a 32-character product identifier:

    SELECT prp.id, prp.name
    FROM ProductRatePlan prp
    WHERE prp.productid = to_uuid36('2c92c0f86680fd090166a424179630c0')
    
    id,name
    2c92c0f8-6329-99a2-0163-c016fabe8ea8,Monthly Unlimited
    2c92c0f8-6329-99a2-0163-b10b3b003e43,Monthly Capped
    ...
    
  • Retrieve the 32-character identifiers of the rate plans in a product:

    SELECT to_uuid32(prp.id) AS id32
    FROM ProductRatePlan prp
    WHERE prp.productid = to_uuid36('2c92c0f86680fd090166a424179630c0')
    
    id32
    2c92c0f8632999a20163c016fabe8ea8
    2c92c0f8632999a20163b10b3b003e43
    ...
    
  • Retrieve the number of subscriptions owned by each customer account:

    SELECT a.accountnumber, count(s.id) as subs
    FROM Account a
    JOIN Subscription s ON a.id = s.accountid
    GROUP BY a.accountnumber
    
    accountnumber,subs
    A00000977,2
    A00000464,1
    A00000985,4
    ...
    
  • Retrieve the yearly average invoice amount for an account:

    SELECT year(i.invoicedate) as year, avg(i.amount) as avg
    FROM Invoice i
    WHERE i.accountid = (SELECT a.id FROM Account a WHERE a.accountnumber = 'A00118294')
    GROUP BY year(i.invoicedate)
    ORDER BY year(i.invoicedate)
    
    year,avg
    2017,195.200000
    2018,230.000000
    2019,193.587000
    

Available Tables

Zuora Link supports the tables listed below. Depending on the features that are enabled in your Zuora tenant, some tables may not be available in Zuora Link. You can use the SHOW TABLES statement to retrieve a list of the 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 Zuora Link and data sources is that tables in Zuora Link are not pre-joined to other tables. When you query a table in Zuora Link, 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:

Zuora Link supports the following tables:

Table Each Row Represents Columns

Account

A customer account

Use DESCRIBE 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 DESCRIBE to list the available columns. For relevant descriptions, see AccountingPeriod in the SOAP API documentation

AccountingCode

An accounting code

Use DESCRIBE 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 DESCRIBE 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 DESCRIBE to list the available columns

BillingRun

A billing run

Use DESCRIBE to list the available columns. For relevant descriptions, see BillRun in the SOAP API documentation

Contact

A contact person. For example, the Bill To contact of a customer account

Use DESCRIBE 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 DESCRIBE to list the available columns. For relevant descriptions, see ContactSnapshot in the SOAP API documentation

CreditBalanceAdjustment

Deprecated if the Invoice Settlement feature is enabled

An adjustment to the credit balance of a customer account

Use DESCRIBE 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 DESCRIBE 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 DESCRIBE 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 DESCRIBE to list the available columns

CreditMemoItem

Only available if the Invoice Settlement feature is enabled

A line item in a credit memo

Use DESCRIBE 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 DESCRIBE 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 DESCRIBE to list the available columns

CreditTaxationItem

Only available if the Invoice Settlement feature is enabled

A taxation line item in a credit memo

Use DESCRIBE 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 DESCRIBE to list the available columns

DebitMemoItem

Only available if the Invoice Settlement feature is enabled

A line item in a debit memo

Use DESCRIBE to list the available columns

DebitTaxationItem

Only available if the Invoice Settlement feature is enabled

A taxation line item in a debit memo

Use DESCRIBE to list the available columns

DiscountAppliedMetrics

A discount rate plan charge that is applied to another rate plan charge

Use DESCRIBE to list the available columns

FXCustomRate

Only available if the Custom Foreign Currency Exchange Rates feature is enabled

A custom exchange rate

Use DESCRIBE to list the available columns

Invoice

An invoice

Use DESCRIBE to list the available columns. For relevant descriptions, see Invoice in the SOAP API documentation

InvoiceAdjustment

Deprecated

An adjustment to an invoice

Use DESCRIBE to list the available columns. For relevant descriptions, see InvoiceAdjustment in the SOAP API documentation

InvoiceItem

A line item in an invoice

Use DESCRIBE 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 DESCRIBE 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 DESCRIBE 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 DESCRIBE to list the available columns

JournalEntryItem

Only available if you use Zuora Finance

A line item in a journal entry

Use DESCRIBE to list the available columns

JournalRun

Only available if you use Zuora Finance

A journal run

Use DESCRIBE to list the available columns

OrderAction

Only available if the Order Metrics feature or the Orders feature is enabled

An order action that is applied to a subscription

Use DESCRIBE to list the available columns

OrderElp

Only available if the Order Metrics feature or the Orders feature is enabled

An "extended list price" metric for an order action

Use DESCRIBE to list the available columns

OrderItem

Only available if the Order Metrics feature or the Orders feature is enabled

An order item

Use DESCRIBE to list the available columns

OrderMrr

Only available if the Order Metrics feature or the Orders feature is enabled

A "monthly recurring revenue" metric for an order action

Use DESCRIBE to list the available columns

OrderQuantity

Only available if the Order Metrics feature or the Orders feature is enabled

A "quantity" metric for an order action

Use DESCRIBE to list the available columns

Orders

Only available if the Order Metrics feature or the Orders feature is enabled

An order

Use DESCRIBE to list the available columns

OrderTcb

Only available if the Order Metrics feature or the Orders feature is enabled

A "total contracted billing" metric for an order action

Use DESCRIBE to list the available columns

OrderTcv

Only available if the Order Metrics feature or the Orders feature is enabled

A "total contract value" metric for an order action

Use DESCRIBE to list the available columns

Payment

A payment

Use DESCRIBE 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 DESCRIBE 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 DESCRIBE to list the available columns

PaymentGatewayReconciliation
EventLog

An event that was processed by a payment reconciliation job

Use DESCRIBE to list the available columns

PaymentMethod

A payment method

Use DESCRIBE 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 DESCRIBE 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

Use DESCRIBE to list the available columns

PaymentPart

Only available if the Invoice Settlement feature is enabled

An applied or unapplied portion of a payment

Use DESCRIBE to list the available columns

PaymentPartItem

Only available if the Invoice Settlement feature is 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 DESCRIBE to list the available columns

PaymentReconciliationJob

A payment reconciliation job

Use DESCRIBE to list the available columns

PaymentReconciliationLog

A reconciled transaction that was processed by a payment reconciliation job

Use DESCRIBE to list the available columns

PaymentRun

A payment run

Use DESCRIBE to list the available columns

PaymentTransactionLog

A transaction from Zuora to the payment gateway associated with a payment

Use DESCRIBE to list the available columns

ProcessedUsage

Uploaded usage that has been processed

For usage that has been processed by the Active Rating feature, see the RealTimeRatingProcessedUsage table

Use DESCRIBE to list the available columns

Product

A product in your product catalog

Use DESCRIBE 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 DESCRIBE to list the available columns. For relevant descriptions, see ProductFeature in the SOAP API documentation

ProductRatePlan

A rate plan in your product catalog

Use DESCRIBE to list the available columns. For relevant descriptions, see ProductRatePlan in the SOAP API documentation

ProductRatePlanCharge

A charge in your product catalog

Use DESCRIBE to list the available columns. For relevant descriptions, see ProductRatePlanCharge in the SOAP API documentation

ProductRatePlanChargeTier

A charge tier in your product catalog

Use DESCRIBE to list the available columns. For relevant descriptions, see ProductRatePlanChargeTier in the SOAP API documentation

RatePlan

A rate plan in a subscription

Use DESCRIBE to list the available columns. For relevant descriptions, see RatePlan in the SOAP API documentation

RatePlanCharge

A charge in a subscription

Use DESCRIBE to list the available columns. For relevant descriptions, see RatePlanCharge in the SOAP API documentation

RatePlanChargeTier

A charge tier in a subscription

Use DESCRIBE to list the available columns. For relevant descriptions, see RatePlanChargeTier in the SOAP API documentation

RealTimeRatingProcessedUsage

Uploaded usage that has been processed by the Active Rating feature

Use DESCRIBE to list the available columns

Refund

A refund

Use DESCRIBE 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 DESCRIBE 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 DESCRIBE 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 DESCRIBE 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 DESCRIBE to list the available columns

RefundPartItem

Only available if the Invoice Settlement feature is enabled

A portion of a refund that is applied to a line item in a credit memo

Use DESCRIBE to list the available columns

RefundTransactionLog

A transaction from Zuora to the payment gateway associated with a payment

Use DESCRIBE to list the available columns

RevenueChargeSummary

Only available if you use Zuora Revenue

A revenue distribution summary of a charge

Use DESCRIBE to list the available columns

RevenueChargeSummaryItem

Only available if you use Zuora Revenue

An item in a revenue distribution summary of a charge

Use DESCRIBE to list the available columns

RevenueEvent

Only available if you use Zuora Revenue

A revenue event

Use DESCRIBE to list the available columns

RevenueEventCreditMemoItem

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue event that is associated with a line item in a credit memo

Use DESCRIBE to list the available columns

RevenueEventDebitMemoItem

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue event that is associated with a line item in a debit memo

Use DESCRIBE to list the available columns

RevenueEventInvoiceItem

Only available if you use Zuora Revenue

A revenue event that is associated with a line item in an invoice

Use DESCRIBE to list the available columns

RevenueEventInvoiceItem
Adjustment

Only available if you use Zuora Revenue

A revenue event that is associated with an adjustment to a line item in an invoice

Use DESCRIBE to list the available columns

RevenueEventItem

Only available if you use Zuora Revenue

A revenue event item

Use DESCRIBE to list the available columns

RevenueEventItemCreditMemo
Item

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue event item that is associated with a line item in a credit memo

Use DESCRIBE to list the available columns

RevenueEventItemDebitMemo
Item

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue event item that is associated with a line item in a debit memo

Use DESCRIBE to list the available columns

RevenueEventItemInvoice
Item

Only available if you use Zuora Revenue

A revenue event item that is associated with a line item in an invoice

Use DESCRIBE to list the available columns

RevenueEventItemInvoice
ItemAdjustment

Only available if you use Zuora Revenue

A revenue event item that is associated with an adjustment to a line item in an invoice

Use DESCRIBE to list the available columns

RevenueEventType

Only available if you use Zuora Revenue

A revenue event type

Use DESCRIBE to list the available columns

RevenueSchedule

Only available if you use Zuora Revenue

A revenue schedule

Use DESCRIBE to list the available columns

RevenueScheduleCreditMemo
Item

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue schedule that is associated with a line item in a credit memo

Use DESCRIBE to list the available columns

RevenueScheduleDebitMemo
Item

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue schedule that is associated with a line item in a debit memo

Use DESCRIBE to list the available columns

RevenueScheduleInvoice
Item

Only available if you use Zuora Revenue

A revenue schedule that is associated with a line item in an invoice

Use DESCRIBE to list the available columns

RevenueScheduleInvoice
ItemAdjustment

Only available if you use Zuora Revenue

A revenue schedule that is associated with an adjustment to a line item in an invoice

Use DESCRIBE to list the available columns

RevenueScheduleItem

Only available if you use Zuora Revenue

a revenue schedule item

Use DESCRIBE to list the available columns

RevenueScheduleItemCreditMemo
Item

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue schedule item that is associated with a line item in a credit memo

Use DESCRIBE to list the available columns

RevenueScheduleItemDebitMemo
Item

Only available if you use Zuora Revenue and the Invoice Settlement feature is enabled

A revenue schedule item that is associated with a line item in a debit memo

Use DESCRIBE to list the available columns

RevenueScheduleItemInvoice
Item

Only available if you use Zuora Revenue

a revenue schedule item that is associated with a line item in an invoice

Use DESCRIBE to list the available columns

RevenueScheduleItemInvoice
ItemAdjustment

Only available if you use Zuora Revenue

A revenue schedule item that is associated with an adjustment to a line item in an invoice

Use DESCRIBE to list the available columns

Subscription

A subscription

Use DESCRIBE to list the available columns. For relevant descriptions, see Subscription in the SOAP API documentation

TaxableItemSnapshot

Only available if the Taxable Item Snapshot feature is enabled

A copy of information that was used in a tax calculation

Use DESCRIBE to list the available columns. For relevant descriptions, see TaxableItemSnapshot in the SOAP API documentation

TaxationItem

A taxation line item in an invoice

Use DESCRIBE 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 DESCRIBE to list the available columns

UpdaterDetail

A payment method update

Use DESCRIBE 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 DESCRIBE to list the available columns. For relevant descriptions if the Active Rating feature is not enabled, see Usage in the SOAP API documentation

User

A user in your Zuora tenant

  • email
  • firstname
  • id
  • lastname
  • username
Last modified

Tags

Classifications

(not set)