Error when using Dynamic Input Tool to replace Where clause

Question

Hi All,

I've gone through the help page and some threads on the Dynamic Input tool, but I'm still unable to debug a particular error.

Basically, I have a WHERE clause in my query, 'WHERE cus.CUSIP IN ('689090AN8')', and Alteryx shows that is processes correctly when I refresh in the Input Data Source template(see below):

Now that I see the query is working fine, I want to replace the values in the WHERE clause with an Excel file pulled in using the File Browse tool.  So, I choose the 'Modify SQL Query' option:

However, when I run this, I get an error:

I tried searching the web and couldn't find much on the specific error.  Does anyone have an idea of what can be causing it?

Thanks,
Sam

samc725 · Answer

@danilang , thank you for that suggestion.  Would you be able to supply any sample workflows to show how you'd go about this?  I see that the formula has some string functions like find and replace.  Is that how you go about the update?

Thanks,
Sam

danilang · Answer

Hi @samc725

Because of the inconsistencies that you're seeing, I don't use Update where clause in any but the simplest cases.  I usually build the entire where clause in a formula tool and then use Replace a specific string to replace the whole thing.  The results are always more predictable

Dan

samc725 · Answer

@danilang , I was able to use the 'Replace a Specific String' for that specific example.

However, I've now tried to use the Update Where Clause again with a numeric column and I'm continuing to get the error.

Here is the WHERE clause within the actual SQL code:

At the moment, I'm using 123 as a placeholder.  And here is what I put in the template:

I continue to get the error, but now it says that 123 isn't in the template.  Note that I also using the simple WHERE statement 'WHERE obligor.ENTITY_ID IN (123)'.  In the example above I'm showing the compound statement with the OR clause because that's ultimately what I need, and I assume it should work.

Thanks for any assistance.

Sam