Happy Business Starts Here

Problems with LIMIT clause in ZOQL

Highlighted
Valued Scholar

Problems with LIMIT clause in ZOQL

Hi All,

I'm using the query method of ZuoraService (SOAP).

 

Using the ZOQL LIMIT clause in order to limit the number of records being returned  nothing happens or I get an error.

For example: if I post the follwing query "select ID from Account limit 5" Zuora returns all the accounts while ignoring the LIMIT clause.

If I post the follwoing query   "select ID from Account limit 5, 10" I get an eror, which says: You have an error in your ZOQL syntax"

 

 

 

Another issue is:

When I use the WHERE clause and DAYS like so: "Select ID from Account WHERE BIllToContact.Updatedate >= 'today - 30 days'

 

Any help would be much appreciated.

Thanks,

Mike.

Tags (1)
8 REPLIES 8
Highlighted
Support SME

Re: Problems with LIMIT clause in ZOQL

Hi Mike,

 

For the reported query 1(LIMIT clause), you can check the following link:

 

http://knowledgecenter.zuora.com/BC_Developers/SOAP_API/D_Use_Cases_Code_Samples/KB_Creating_an_Expo...

By issueing a create() EXPORT, this creates a file of your objects for later download. I tested using Limit clause and it works fine.

For the query2, you can check the below article, you can use the now keyword to specify the current timestamp and today keyword to specify the current day. You can then use the create EXPORT call as described above(for query 1)

 

https://knowledgecenter.zuora.com/DC_Developers/M_Export_ZOQL/G_Dates_and_Datetimes

 

Please check the details and let me know in case of any further concern.






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √

Highlighted
Valued Scholar

Re: Problems with LIMIT clause in ZOQL

Hi @Jyoti_Sinha,

 

Thanks for answering. But I think I was misunderstood .

 

I use the follwing method from the Zuora API: public QueryResult query(string queryString) 

The query()  method itself. And I'm having the problems I've mentioned (below post)

I don't want to export the results into files or anything like that but rather receive a QueryResult which is a stream into my code.

 

As far a I've seen the create() method creates different entities in the Zuora database.

 

Please check that again.

 

Thanks,

Mike.

Highlighted
Support SME

Re: Problems with LIMIT clause in ZOQL

Hi Mike,

 

 Thanks for confirming you are using the query() call, in that case the Limit clause and Today would not be supported.

 






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √

Highlighted
Valued Scholar

Re: Problems with LIMIT clause in ZOQL

Hi @Jyoti_Sinha,

Thanks for checking this issue.

Following your answer, is there any other way to achieve what I want, maybe some kind of a workaround?

Do I have to use the REST API or something like that in order to LIMIT the number of records? Please give some guidence on that or refer me to.

Thanks

Highlighted
Support SME

Re: Problems with LIMIT clause in ZOQL

Hi Mike,

 

You can either use the create() EXPORT as described above or also, I used the AQuA REST API for both the scenarios, sample codes below.

 

This AQuA REST API submits an aggregated list of ZOQL and Export ZOQL queries.  

 

{
"format" : "csv",
"version" : "1.1",
"name" : "Example",
"encrypted" : "none",
"dateTimeUtc" : "true",
"queries"  : [ {
    "name" : "Account",
    "query" : "select Id,Name from Account limit 5,10;",
    "type" : "zoqlexport"
  }]
}
{
"format" : "csv",
"version" : "1.1",
"name" : "Example",
"encrypted" : "none",
"dateTimeUtc" : "true",
"queries"  : [ {
    "name" : "Account",
    "query" : "select Id,Name from Account where createddate >= 'today - 90 days';",
    "type" : "zoqlexport"
  }]
}

Related article: https://knowledgecenter.zuora.com/DC_Developers/T_Aggregate_Query_API/B_Submit_Query

 

Please let me know if it helps.






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √

Highlighted
Valued Scholar

Re: Problems with LIMIT clause in ZOQL

Hi @Jyoti_Sinha,

 

Thanks for the answer. But unfortunately I can't find it helpful.

 

The example you provided creates CSV or gzip files and they must be downloaded upon export completion. The problem is that I need the result as a stream into the code. And suppose I follow the solution you pruposed:

How do I know the status of the export in order to download the file by code?

Do I have to check the status manually once in a while? How?

 

Isn't there anything like the query() method through REST to get the results right away?

 

Thank you,

Mike

 

Highlighted
Guru

Re: Problems with LIMIT clause in ZOQL

@_mikeSmith the LIMIT clause is simply not supported in standard ZOQL but it is in the Export ZOQL used by the CSV exports.

 

The one issue with applying a LIMIT to a standard query is that there is also no support for ORDER BY which means if the query would have resulted in 5 records and you limited it to 2 you have no deterministic way of knowing which 2 would be returned to you.

 

The query API does have support for a batchSize where you can specify how many records from the query are returned at a time and then you can use queryMore to get the rest of the "pages" of results if you desire. I dont know the specifics of your use case, but this may be helpful if you need to work with smaller chunks of data streaming in.

 

Perhaps if you give more details on the goal of your use case I can share more insights into best practices.

Highlighted
Valued Scholar

Re: Problems with LIMIT clause in ZOQL

Hi @feisley, @Jyoti_Sinha,

I've finally resolved the querying issue using the ZOQL export  and then downloading (using API provided by Zuora) the resulted CSV file.

Thanks,

Mike.