CODEWITHSUNDEEP
restzuorazuora-soap
sag
sag

Reputation: 5461

ZOQL Execution via Zuora REST API

I am trying to execute some ZOQL via Zuora REST API.

I've referred this doc and I am using REST endpoint v1/action/query to execute ZOQL.

First I tried with very simple request and got the result back

{
  "queryString": "select AccountId, FirstName, LastName from contact"
}

Now I tried query with asterisk like below

{
  "queryString": "select * from contact"
}

But I got below error

{
  "faultcode": "fns:MALFORMED_QUERY",
  "faultstring": "You have an error in your ZOQL syntax",
  "detail": {
    "MalformedQueryFault": {
      "FaultCode": "MALFORMED_QUERY",
      "FaultMessage": "You have an error in your ZOQL syntax"
    }
  }
}

From here, I found that ZOQL supports asterisk. I even got the same error for ZOQL which involves multiple objects. Like

SELECT Subscription.Name, Account.Name FROM Subscription WHERE Subscription.Status='Active' AND DefaultPaymentMethod.CreditCardType='Visa'

EDIT Above query does not work in Zuora SOAP API as well

How can I execute query with asterisk in Zuora REST API or in Zuora SOAP API?

Upvotes: 2

Views: 2473

Answers (2)

WillC
WillC

Reputation: 2115

When you use a * in query it is because:

1) you want all the fields available

OR

2) you want to find out WHAT fields are available.

For the later case, use the Describe function of the REST service as in:

https://{servicename}.zuora.com:####/v1/describe/Invoice

which will return an XML description of the Invoice (or any other) object as:

<?xml version="1.0" encoding="UTF-8"?>
<object href="https://services470.zuora.com/apps/api/describe/Invoice">
    <name>Invoice</name>
    <label>Invoice</label>
    <fields>
        <field>
            <name>AccountId</name>
            <label>Account ID</label>
            <selectable>true</selectable>
            <createable>true</createable>
            <updateable>false</updateable>
            <filterable>true</filterable>
            <custom>false</custom>
            <maxlength></maxlength>
            <required>true</required>
            <type>text</type>
            <contexts>
                <context>soap</context>
            </contexts>
        </field>
        <field>
            <name>AdjustmentAmount</name>
            <label>Adjustment Amount</label>
            <selectable>true</selectable>
            <createable>false</createable>
            <updateable>false</updateable>
            <filterable>true</filterable>
            <custom>false</custom>
            <maxlength></maxlength>
            <required>true</required>
            <type>decimal</type>
            <contexts>
                <context>soap</context>
                <context>export</context>
            </contexts>
        </field>
    <!-- All fields for Invoice...ETC   -->
</fields>
</object>

Upvotes: 3

MordechayS
MordechayS

Reputation: 1546

Queries With Asterix In ZOQL:

In short: You just can't use asterix.

More info:

From Zuora KnowledgeCenter:

No Wild Card Support

You cannot use the asterisk wild card (*) for field names with a query() call. You must explicitly specify a field name.

The above source you mentioned stating that you can use asterix, is NOT about ZOQL, but about Export ZOQL.

Export ZOQL is different from ZOQ, as stated in the docs above:

Zuora Export ZOQL (Zuora Object Query Language) is the query language used to create Exports with the Export object in the Zuora SOAP API. Zuora Export ZOQL is similar to our general ZOQL, with a few differences. The biggest difference is that with Exports, you query a Zuora data source, not a SOAP API object.

Hope this helps you.

Good luck!

Upvotes: 3

Related Questions