Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Error when using Dynamic Input Tool to replace Where clause

samc725
6 - Meteoroid

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):

samc725_0-1614798201377.png

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:

 

samc725_1-1614798568629.png

 

samc725_2-1614802760835.png

 

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

samc725_4-1614802943981.png

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

6 REPLIES 6
JosephSerpis
17 - Castor
17 - Castor

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?

samc725
6 - Meteoroid

Hi @JosephSerpis , thank you for your response.  Yes I tried that and unfortunately I still get the same error.

danilang
19 - Altair
19 - Altair

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

samc725
6 - Meteoroid

@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:

samc725_0-1614886271643.png

 

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

samc725_1-1614886413771.png

 

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

 

 

danilang
19 - Altair
19 - Altair

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
6 - Meteoroid

@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

Labels