Happy Business Starts Here

Re: How to use the result set of a data query as input to another data query?

Highlighted
Tutor

How to use the result set of a data query as input to another data query?

I have a data query task that produces a csv file of subscription-ids.  It's just one column. I would like to follow this with another retrieve task that takes this list of subscription-ids as an input.

 

To elaborate, suppose my list of subscription-ids produced by the data query is called 'list1'. I would like to perform a data query similar to: select * from Subscriptions where Id IN list1.

 

My overall goal is identify subscriptions containing discount rate plans that are ending in two weeks.  For each subscription, I would like to identify the corresponding non-discount rate plan that will kick in, and its regular price (charge).

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Zuora Staff
Zuora Staff

Re: How to use the result set of a data query as input to another data query?

SQL can become unreadable very quickly, so I understand.  How about trying to use the with clause to better organize subqueries?

 

The way Workflow works potentially makes what you want to do perform inefficiently.  That is, you have to send your Data Query result into an Iterate task, which decomposes the result for you to be able to handle one record at a time.  In your case, you'd get one Subscription Id at a time.

 

I am not aware of other options that allow you the same level of flexibility in your conditionals as Data Query.

View solution in original post

3 REPLIES 3
Highlighted
Zuora Staff
Zuora Staff

Re: How to use the result set of a data query as input to another data query?

Have you considered using your original Data Query as a subquery?  For instance:

select *
from subscriptions
where id in (<your original query here>)
Highlighted
Tutor

Re: How to use the result set of a data query as input to another data query?

Hi Chi, thanks for following up.  Yes, I am actually considering doing this if I don't find another solution.  The main reason that I'm looking for an alternative is because the real query that I need to do is much larger, and a nested query would cause it to double in size.  I need my query to be easily 'readable'/'understandable' by other members of my team, which is why I'm looking to separate it, or break it into smaller steps (tasks).  

 

Is there any sequence of tasks that I can use to pass a result-set forward to another task?  I am not an expert in Workflows, so I am not aware of all the capabilities of all the tasks.  I am curious if there is any pair of tasks that I can use in this regard.  The only other option that comes to mind is to iterate over the set with a loop but I'm not sure this would be best.

 

Or is there perhaps an object query or export query that I can create to find discount rate-plans ending in X days, that will group together the other rate plan that will replace it?

Highlighted
Zuora Staff
Zuora Staff

Re: How to use the result set of a data query as input to another data query?

SQL can become unreadable very quickly, so I understand.  How about trying to use the with clause to better organize subqueries?

 

The way Workflow works potentially makes what you want to do perform inefficiently.  That is, you have to send your Data Query result into an Iterate task, which decomposes the result for you to be able to handle one record at a time.  In your case, you'd get one Subscription Id at a time.

 

I am not aware of other options that allow you the same level of flexibility in your conditionals as Data Query.

View solution in original post