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
Hi @samc725 have you tried removing the quotes in the Text to replace and just leaving the CUSIP as I imagine the data in your excel does not have quotes round the CUSIP?
Hi @JosephSerpis , thank you for your response. Yes I tried that and unfortunately I still get the same error.
Hi @samc725
This error is normally returned when the string that you're trying to replace in the template can't be found. Can you post an image of the template sql statement, or at least the where clause
Dan
@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
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
@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