You are viewing limited content. For full access, please sign in.

Question

Question

Running an 'order by' on the results of my Query Data activity.

Workflow
Updated June 1, 2018
asked on May 31, 2018

Hi,  I would like to run an nested 'order by' on the result of my query data activity which uses a rather complex SQL view.  I've looked in the toolbox but couldn't really see anything I could use. Does anyone know if it's possible to do this ?

Thanks,

Colin.

0 0

Replies

replied on May 31, 2018

As far as I know, the best way to do this is with a Custom Query instead of a Query Data activity.

Instead of using Query Data pointed at your view, you would just write out a query for your view with the Order By you require and put that directly into the Custom Query.

1 0
replied on June 1, 2018 • Show version history

Thanks Jason.  Now I've understood what you meant (it took a while), I'll give that a go :-)

Colin.

PS - I've run the 'order by' against the view as a customer query.  Problem I have now is that the conditional sequence cannot see the fields from that query. When I edit one of the conditions and go to Token\For Each Row', all I see is 'iteration' and not the field names. Any ideas ?

0 0
View 3 previous replies
replied on June 1, 2018

After you modify the query of a Custom Query activity, you must run a test before the columns are available as tokens in your following activities.

1 0
replied on June 1, 2018

Thanks Bert. Yes, I eventually found that out :-) The problem lies with the fact that the view takes 12 minutes to run and doing a test on it, times out after 30 seconds. So lord knows how the person that wrote the workflow got it to work (and it does work every Friday morning). I'll have to ask them when they are back in the office.  At least you've confirmed my findings so thanks again Bert.

Colin.

0 0
replied on June 1, 2018

Since the view takes so long to run, I think I would use a different approach.

In SQL, create a table from the output of your view.  Then create a scheduled task in SQL that runs every Thursday night and truncates the table before populating it from the view output (or a query that produces the same output as the view).

Then in Workflow, query the local table instead of the long running view.  You get the same results, but the query will run much faster and avoid the timeout conditions.

2 0
replied on June 1, 2018

That sounds like a great idea Bert, thanks.  I'll look in to setting that up and see how it goes. Thanks so much for your help :-).  I'll report back if I can  yes

0 0
replied on June 1, 2018

If you need the target table to always be available, what I do is store results to a temp table first, then truncate and update the table after the temp table is populated.

The advantage of this approach is that you don't have any "down time" while the query is running and you can set it up so that if the query fails you still retain the old data.

1 0
replied on June 1, 2018

Thanks Jason.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...