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

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?

Input

Accepted answers

cjaneczko

If you are using the Update Where Clause instead of Replace a string, you don't want to use the summarize tool to concatenate them. You want the format to be in a list when coming into the Dynamic Input tool. The dynamic input will concatenate it on its own based on the list of TINs. And it will group them so they dont exceed your specified character limit as well. Try it without the summarize tool.

All comments

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-function-decfloat-0

can you try concatenating below method and check

Please mark as solution if it works

Concat.yxmd

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.

cjaneczko

cowannbell

Thank you. That was the issue. Works great now.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703