Happy Business Starts Here

Dynamic Zoql Query

Highlighted
Tutor

Dynamic Zoql Query

Hello,

 

I'm trying to write data query to try and capture items created within the last quarter. Is there a way to dynamically set up the date so it can achieve that instead of having to manually change the date each time the query needs to run?

Our year starts in March, so QUARTER(date) is not ideal since the quarter function is for Jan-Mar, Apr-Jun, Jul-Sept and Oct-Dec whereas our quarter is Mar-May, Jun-Aug, etc.

 

I know with some SQL, you can do a to_date function along with some fancy hacking of today to achieve it... is there something similar with ZOQL?

 

Thanks in advance!

3 REPLIES 3
Highlighted
Valued Scholar

Re: Dynamic Zoql Query

Hi Ichin,

 

If you're using Export ZOQL, then this is an easy task. You can simple use a query such as 

select * from tablename where datefield>'today - 90 days'

or use weeks/months or for more support browse through:

https://knowledgecenter.zuora.com/Central_Platform/Query/Export_ZOQL/G_Dates_and_Datetimes

If you're using standard ZOQL, then this becomes a tedioud task.

Might have to design a function from scratch in the query or create multiple condtitions.

Highlighted
Tutor

Re: Dynamic Zoql Query

Hi Sayam,

 

Thanks for the advice. I'm not sure how to create an Export ZOQL query... I'm building the query out of the Zuora UI via Platform > Data Query.

 

Can I write functions within a data query?

Highlighted
Tutor

Re: Dynamic Zoql Query

As an addition to the response, does ZOQL allow for case statements? I'm trying to read up on some of the knowledge center articles, but haven't come across anything that says I can/can't.