Dynamic Input IN-DB

[gcosta]

Hi everyone, looking for some help. 

I want to dynamic update my SQL Where Clause. However, there is a limitation on the qty of records you can have in the Where clause (around 16k). I have a list with more than 100k records,

I am looking for a solution where I can break this in batches of 15k and dynamic send them to the Dynamic Input IN-DB for the update of the where clause. I am not sure how can I make the Dynamic Input IN-DB run in batches. 

Thank you so much,

Gabriel 

[alexnajm]

@gcosta I was going to recommend a batch macro, but I don't think this is doable In-DB - would this option work? In-DB Batch Macro - Alteryx Community

[Robin_McIntosh]

@gcosta Could you do something like this where you create groups of your data and then pass each group into a Dynamic Input In-DB?

You would change the formula denominator to 15K or whatever batch size you need.  This will then create groups or batches of 15K records.

[OllieClarke]

@gcosta 

This might not be the most efficient, but what if you used a Join as a where clause? Then you can read in all 100k values and get your output where they match?

Ollie

[gcosta]

Hi @Robin_McIntosh, thanks for replying. Not really because of the way that the Dynamic IN DB tool works. You can connect data like this. At least not in this way

[gcosta]

Hey @alexnajm , thanks for the help. I ve tried. Did several iterations trying to get a batch macro working but doesn´t look like I can do that on the Dnamic Input IN-DB tool. I was wondering if perhaps I just use the standard Dynamic Input tool and see if I can make the changes and run the queries several times. 

[gcosta]

Hi @OllieClarke, thanks for the help. I am not sure I am following you. Could you please share an example of what you mentioning? 

Thank you so much!

[OllieClarke]

@gcosta 

This is what I was thinking:

Does that help?

[Robin_McIntosh]

@gcosta An updated example where in the In-DB part is in a Batch Macro.  You feed over the connection file and query based on group (batch) and then all group results are then combined back in the workflow.

In my example, I'm summarizing (concatenating) the values to be queried based on each group.

Then use the formula tool to build each batch query and also bring in the In-DB Connection File.

And then within the Batch Macro, the In-DB query will run for each bacth or group.

[gcosta]

Hi @Robin_McIntosh . Thank you so much for the example. I think this can do the job. 

Can you share the configuration of the macro? I am trying to set this right but I am getting the follow error: 

Thank you!!