Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Dynamic Input Tool With Text Where Clause that includes an Apostrophe Not Working

Highlighted
Asteroid

I have a workflow that includes a dynamic input tool that has a text-based where clause.  When the Text includes an Apostrophe, I'm not getting any results back from my SQL query. If I use the same query in Microsoft's Sql Server Management Studio it works fine. 

 

Here is how my dynamic tool is configured:

 

Capture2a.PNG

 

Capture2b.PNG

The Text going into the Where Clause is:  Gio's Calling

 

I have a Regex tool before my dynamic input tool that takes that input and escape the Apostrophe:

 

Input to Regex Tool: 

 

Capture2c.PNG

 

 Output  From Regex Tool with Escaped Apostrophe

Capture2d.PNG 

 

The resulting query is:

 

Select CHART.CHART_DATE, RACE.TRACK_CODE, RACE.NUMBER, RACE.TYPE, RACE.SURFACE, ENTRY.HORSE, RACE.RACE_Id, ENTRY.ENTRY_Id From CHART Join RACE On CHART.CHART_Id = RACE.CHART_Id Join ENTRY On RACE.RACE_Id = ENTRY.RACE_Id Where ENTRY.HORSE In ('GIO''S CALLING') Order By ENTRY.HORSE

 

The query works great in Microsoft' SQL Server Management Studio but the dynamic input tool does not return any results. 

 

Does anyone have any thoughts on where I'm going wrong. 

 

UPDATE:  HERE'S SOME ADDITIONAL INFORMATION - THE QUERY IN THE DYNAMIC INPUT WORKS FINE IN THE YXMD VERSION OF THE WORKFLOW, BUT NOT IN THE YXMC VERSION.   UNFORTUNATELY I NEED THE YXMC VERSION TO WORK. 

 

Thanks,

Asteroid

I've figured out what was going wrong. 

 

In the YXMC version of the script I was using a V_WString with a size of 100.  When I changed it to V_String with a size of 50 it worked like a charm.  

 

Thanks

Jim

Labels