Hello everyone, I have been trying to work on this for a while now (am new to alteryx) and am at the point where I just want to bust out some python and do it that way.
Issue is that we use Oracle, so the in clause limits to 1000. I am just testing, but in the current dynamic data I pulled I have around 2700 records.
What I have done so far is broken down a count of 0,1,2 in a list.
What my thought was is that on the first iteration (0) I could pass the first 1000, then on iteration (1) I could pass the second 1000, then on interation(2) pass the rest.
I have a dynamic sql going as well. So What I was doing is grabbing the first random Id's and passing those in the in clause using dynamic input.
Could someone please point me in the correct direction?
So far I am trying to do a batch macro, but am kind of lost in terms of adding a custom loop count, then also passing the dynamic data into the dynamic input for that iteration.
I should also mention I am quite new to SQL as well, but found a post on here about this:
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;
The SQL running is about 400+ lines.
Solved! Go to Solution.
There's an easy (non-batch) work around for this. It relies on the feature that the Dynamic Input tool batches and unions all the results together. So rather than inputting 1 record with 2700 conditions into the Dynamic Input (which doesn't work), make 3 rows with <=1000 conditions in each and the Dynamic Input tool will output them all together.
I've attached an example solution I built last week to show how to use a RecordID and Formula tool to achieve this.
I got it to at least start processing. I have gone through your workflow many times to see the data types and see that they are all wrapped in quotes at the end. My data types are literally the same as yours, but cannot get them to do this. At thigs point they are just showing as example:
1234,1235,153,654363,65436435
Does this ring any bells?
nevermind I found the concat options at the bottom of the sum block.
Okay, so now it processed, but is not working. I have successfully gotten this to work with < 1000 as an w_vstring
Here is my replace in dynamic input
I got it working! Thank you so much.
For whoever else uses this, the issue was the "Update WHERE Clause"
I deleted that rule, and changed it to replace string
I changed
'placeholder'
to Replacement_Field
With this method, are we doing queries multiple times, vs one query and looking at the resuls?
I'm glad you were able to get things working!
@joejoe317 wrote:With this method, are we doing queries multiple times, vs one query and looking at the resuls?
If you're using the solution I posted to create multiple records, each record going into the Dynamic Input is a query that is executed. The Dynamic Input will execute those queries one by one, then the results from all queries are union-ed together before they are output from the tool. This process should be indicated by the Results window that outlines each query executed.
Thats what I thought, Thanks Charlie.. You have been helpful today.
I just stumbled on this (unexpected?) behavior of the Dynamic Input by pure luck by myself. Then googled it and found this post about it.
As joejoe317 i thought I'd need a batch macro to have the Dynamic input loop through all the iterations of my IN values, however to my surprise (Dynamic Input connected to Oracle) it did this weird internal loop on its own and returned the full dataset with no need for macros. Shocked, surprised, suspicious... so many emotions..