Dynamic Input "Update Where Clause" problem with single quote

[STHOMAB]

I am using the Dynamic Input tool for a SQL Server query, which includes an OPENQUERY call to a DB2 database.

OPENQUERY requires two single quotes around each WHERE IN value (to escape it to one).

SELECT TMP.*             

FROM OPENQUERY(DB2PROD,'                 

SELECT   FIELD1,  FIELD2

FROM     TABLE                              

WHERE   FIELD1 IN ('''')

AND FIELD2 IN (''ABC'',''DEF'')

') AS TMP    

When using Modify SQL Query > SQL: Update WHERE Clause, Alteryx generates the WHERE IN statement with only one single quote around each value instead of two single quotes.

I created a new field where FIELD1 is already encapsulated in single quotes (thinking the existing single quote + the one Alteryx will add = 2 single quotes), but then Alteryx places three single quotes around each value when it generates the WHERE IN statement.

I can’t get this figured out, hoping that someone else has solved this and can help me out.

Thank you.

STHOMAB

[jamielaird]

HI @STHOMAB 

Have you tried using Replace a Specific String instead of Update Where Clause?

[STHOMAB]

Hi jamielaird, I did look into the Replace Specific String option, but didn't see how that could work with dynamically feeding in the replacement values. It looked as though I would need to pre-specify the replacement value. The replacement string is too long to feed into a single IN statement, I need to loop it several times.

[jstewart]

STHOMAB, 

In the past I have used a formula tool to put quotes around the variable and a summary tool to concatenate all incoming variables with a comma delimiter which allows you to replace the complete query sting once rather than alliterative. Is this what you are saying become too long or have you tried this yet? I'm all ears is there is another approach as I have had this problem as well however this work around has been sufficient for me.

Cheers, 

Jack 

[STHOMAB · Accepted solution]

Hi Jack,

Yes, the resulting string was too long to inject into the WHERE IN statement.

Attached is the solution that worked for us. A coworker with strong Alteryx experience helped us out.

The Summarize tool outputs a series of comma delimited variables surrounded by single quotes. This can be fed into the OPENQUERY WHERE IN statement in a Dynamic Input tool. The 'Modify SQL Query > SQL:Update WHERE Clause' adds the standard single quotes around each value, to the already existing single quote. Now I have the needed two single quotes around each variable. 

The Dynamic Input tool shows the red error exclamation mark until the workflow runs. 

Best,

Sabine