Happy Business Starts Here

Valued Scholar

About ZOQL restrictions

We are asking for requests and workarounds regarding ZOQL restrictions.

Currently, I understand that you can specify only one object and not complex queries.
However, it is necessary to display a combination of Invoice and invoiceItem. Although it can be realized at the present moment, the possibility of reaching the upper limit that can be specified in the Where clause has come up.
Would you consider the correspondence so that you can refer to two objects?
Is there any workaround at the moment?
For example, you can call information from Zuora's report set from an external API and get information.
Thank you very much.

5 REPLIES 5
Zuora Documentation

Re: About ZOQL restrictions

Hi @Hajime,

 

It sounds like Export ZOQL is what you're looking for, rather than ZOQL. The main difference between Export ZOQL queries and ZOQL queries is that Export ZOQL queries enable you to retrieve fields of multiple types of object with a single query. As you say, with ZOQL queries you can only specify one object per query.

 

For example, you could query the InvoiceItem data source to get a combination of InvoiceItem and Invoice data:

 

select ChargeAmount, ChargeName, Invoice.InvoiceNumber from InvoiceItem

 

This query selects the charge amount, charge name, and invoice number of every InvoiceItem. You could use a 'where' clause to filter the results if you need to.

 

To run this query, you can use the AQuA API or CRUD: Create Export. You can't use Query to run this query because Query does not support Export ZOQL queries (it only supports ZOQL queries).

 

We're working on a tutorial about data queries/exports in Zuora. I've just posted a preliminary version in reply to another question, here:

https://community.zuora.com/t5/API/Why-is-accountId-not-denoted-as-an-exportable-field-for-objects/m...

 

That reply addresses your question in more detail and also gives an example of how to run an Export ZOQL query using the AQuA API.

 

Hope this helps, and really appreciate any feedback you have on how we can explain this topic better in the documentation!

Valued Scholar

Re: About ZOQL restrictions

Please let me ask you a question in addition.
"ExportZOQL" is extracted in two stages for processing on the online screen, which may be unsuitable.
Is there a candidate other than "ExportZOQL" as a function to refer to multiple objects with ZOQL?

Highlighted
Zuora Documentation

Re: About ZOQL restrictions

You are right - using Export ZOQL is always a multi-step process:

 

Step 1 is to send the Export ZOQL query for processing

Step 2 is to check the status of the data export job

Step 3 is to download the data file

 

If you require a single transaction, you can only use ZOQL via Query. But you won't be able to refer to multiple objects this way.

 

Would you be able to post some more details about your use case? It may be that one of the non-ZOQL API endpoints would meet your needs.

Valued Scholar

Re: About ZOQL restrictions

Thank you for your reply.
The user inquires the screen from the browser and displays the same as the invoice of Zuora.
In this time, "RatePlanName" is added to the detailed line of this display and it is now displayed.
The base of the detail line is InvoiceItem. However, RatePlanName can not be used directly from InvoiceItem. Therefore, we refer to Invoice and get RatePlanName. Because want to do such a query at one time is the time of the question content.

Zuora Documentation

Re: About ZOQL restrictions

If you want to get information about InvoiceItem objects and RatePlan objects together, you will need to use an Export ZOQL query like this:

 

select Quantity, ChargeAmount, ChargeName, RatePlan.Name from InvoiceItem

 

Unfortunately, this requires more than one API call to get the data. I've confirmed with our Engineering team that there isn't a way to get InvoiceItem data along with RatePlan data using a single step.

 

If you used other REST API operations to get the same data, I think you would need to get RatePlanCharge data from InvoiceItem data, then get RatePlan data from RatePlanCharge data. So it seems to me like Export ZOQL is the best option in your case.

 

Hope this helps!