Dynamic Input tool update comes with sql error SQL0420N Invalid character found in a char

[cowannbell]

I have a work flow that takes a user input of Tax IDs and concatenates then into a string field like this: '123456789','789456123'

That then is sent to the sql statement via the dynamic input field.  It works fine if I use the Replace a string field but if I try using Update WHERE clause, I get the Invalid Character error.

What can I do to fix this?

[Raj]

@cowannbell what message do you get?

[cowannbell]

Error: Dynamic Input (34): Error SQLExecute: [IBM][CLI Driver][DB2] SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018

[apathetichell]

can you confirm the data you are feeding in (ie column Concat_Tins) is in the format of 'value1','value2' in Alteryx - if so - can you post a screengrab of it (truncated should be fine - assuming there are no ) at the end and the last character is a single quote (')? My hunch is that this doesn't match the format of the IN tool is expecting - I see some documentation around this error relating to math functions for non-math values - but I think in this case it may be the underlying data does not fit what the function is expecting.

[Raj]

@cowannbell remove the inverted Comas in the text to replace field.
this should solve the problem

[Swathi]

Are you using + to concatenate ??

look at this why- https://www.ibm.com/support/pages/sql0420n-invalid-character-found-character-string-argument-functio... 

can you try concatenating below method and check 

Please mark as solution if it works

[cowannbell]

I'm pretty sure the concatenation is not the problem.

I use the summary tool just like you and select the field and do concatenate.  My data comes out like this, '123456789','789456123'

The SQL that it's updating is in('123456789')

When I use this it works fine.  Replace a Specific String: '123456789'

When I use this, then it throws the error.  SQL: Update WHERE clause: T4.AAAAAA_TAX_No in ('123456789')

The reason I want to use the 2nd is because I can limit the character in clause.

[cowannbell]

It looks just like what I put in my initial request.  Like I said, it works perfect if I use Replace a specific string, it works fine.  It just won't work when I use the Update WHERE clause.  I want to use the Update WHERE clause so I can limit the characters

[cjaneczko]

I have run into this problem and had to use the Replace string and generate the WHERE clause in a formula tool after the Summary tool and before the Dynamic input tool. See below an example of where I have done this.

In your example you would want to create your WHERE statement in the formula tool and then in your SQL statement change the WHERE clause to "where 'Update This String'" instead of how it is now "WHERE clause: T4.AAAAAA_TAX_No in ('123456789')"

Formula Tool formula:

T4.AAAAAA_TAX_No in ("+[Concat_Tins]+")

Example: (Ignore the black bars after the statement as I have also added more SQL into my particular query, but it should work the same.

[cowannbell]

The problem with this, is that when using the replace a specific string I don't get the option, I don't get the charcter limit for IN clause option.