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

STHOMAB

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

OPENQUERY_AddTwoQuotesToInStatementMembers.yxmd

UpdateWhereClause.png