Skip to main content

Data Query FAQs

Zuora

Data Query FAQs

  1. Q: Does Data Query support the currency conversion?

    A: No, not yet.

  2. Q: What are some common SQL errors that I should look out for?

    A:

    • Error: Column region_c cannot be resolved

      Solution: You are missing a reference to the table that includes region__c. Select or join from that table in your query and try again.

    • Error: Query exceeded the maximum time limit of 3600.00s

      Solution: This error is thrown when you reach one of the system limitations in Data Query. Queries running for longer than 1 hour will be canceled. Your query might be running for a long time for many reasons: you are doing complex JOINs across many tables without filters in your WHERE clause, or you are doing complex aggregate functions in your query, or you are running many SELECT clauses in one query, and so on. Try to address one or several of these issues to improve your query performance.

    • Error: Table owl.data.acccount does not exist

      Solution: The table you are trying to access either does not exist in your tenant or you have a spelling error in your query. Run SHOW TABLES to confirm that you have that table in your tenant.

  3. Q: Can I submit a ZOQL query to Data Query?

    A: No. Only SQL queries are supported. You must convert ZOQL statements to SQL statements before submitting data queries. See ZOQL to SQL conversions for more information.

  4. Q: What shall I do for the upcoming column deprecation in January 2023?

    A: The following columns will be end-of-life in January 2023. Zuora will no longer return these columns in the Data Query schema after these columns are deprecated. To minimize any impact on your queries, it is highly recommended to review your queries now and remove the columns to be deprecated. From now on, use the JOIN syntax to add these columns to your queries. An example is provided below. For any assistance please contact Zuora Global Support.

    The following columns in the invoiceitem object will be end-of-life:

    • accountid
    • parentaccountid
    • billtocontactid
    • soldtocontactid
    • defaultpaymentmethodid
    • productrateplanchargeid
    • rateplanid
    • productrateplanid
    • amendmentid
    • Productid

    The amount column in the PaymentPart object will be end-of-life.

    The accountingcode column in the ProductRatePlanCharge object will be end-of-life.

    The following columns in the ProductRatePlanChargeTier object will be end-of-life:

    • price
    • priceformat

    The journalentryId column in the following objects will be end-of-life: 

    • CreditBalanceAdjustment
    • CreditMemoApplicationItem
    • CreditMemoItem
    • CreditTaxationItem
    • DebitMemoItem
    • DebitTaxationItem
    • InvoiceAdjustment
    • InvoiceItem
    • InvoiceItemAdjustment
    • InvoicePayment
    • JournalEntryItem
    • NonSubscriptionInvoiceItem
    • PaymentApplicationItem
    • PaymentApplication
    • RefundApplication
    • RefundApplicationItem
    • RefundInvoicePayment
    • RevenueEventItem
    • RevenueEventItemCreditMemoItem
    • RevenueEventItemDebitMemoItem
    • RevenueEventItemInvoiceItem
    • RevenueEventItemInvoiceItemAdjustment
    • TaxationItem

    Here is an example of using the JOIN syntax to add the columns to a query.

    SELECT InvoiceItem.id, Account.id as aid, Invoice.invoicenumber as invn, ProductRatePlan.id as prpid,ProductRatePlanCharge.id as prpcid, RatePlan.id as rpid, RatePlanCharge.id as rpcid
    FROM invoiceitem
    JOIN productrateplan on invoiceitem.productrateplanid = productrateplan.id
    JOIN rateplancharge on invoiceitem.rateplanchargeid = rateplancharge.id
    JOIN productrateplancharge on invoiceitem.productrateplanchargeid = productrateplancharge.id
    JOIN rateplan on invoiceitem.rateplanid = rateplan.id
    JOIN account on invoiceitem.accountid = account.id 
    JOIN invoice on invoiceitem.invoiceid = invoice.id
    JOIN subscription on invoiceitem.subscriptionid = subscription.id
    JOIN account on invoice.accountid = account.id
    JOIN rateplancharge on invoiceitem.rateplanchargeid = rateplancharge.id
    JOIN rateplan on rateplancharge.rateplanid = rateplan.id
    JOIN subscription on rateplan.subscriptionid = subscription.id
    JOIN productrateplancharge on rateplancharge.productrateplanchargeid = productrateplancharge.id
    JOIN productrateplan on productrateplancharge.productrateplanid = productrateplan.id
    

    For future reference, please find the following documents: