Escaping single quotes in expression builder

Question

Hi,

I'm trying to use the following expression inside a query of a source table in a dataflow:

concat('SELECT a.col1, col2, col3, col4 FROM Table1 a, Table2 b ',  
'WHERE a.col1=b.col1 AND col3 >=', '\'', $last_year_date,  
'\'', ' AND col2 IN ', replace(replace(replace(toString($ticker_groups), '[', '('), ']', ')'), '"', '\''))  

last_year_date is a string parameter that has to look like a date and therefore, it needs to have a couple of single quotes so that the SQL runs.
ticker_groups is a string array parameter. I have to replace a few characters (brackets for parenthesis and double quotes for single quotes).

I keep getting this kind of errors no matter what combinations of characters I try in my expression builder:

Spark job failed: {
"text/plain": "{\"runId\":\"859a0c64-d8c1-47ef-9558-448df6c58d21\",\"sessionId\":\"d717f120-41c6-4117-bac7-45787cc27e6e\",\"status\":\"Failed\",\"payload\":{\"statusCode\":400,\"shortMessage\":\"com.microsoft.dataflow.broker.InvalidOperationException: DSL compilation failed: DF-DSL-001 - DSL stream has parsing errors\nLine 14 Position 114: token recognition error at: '\\n'\nLine 14 Position 181: token recognition error at: '\\n'\",\"detailedMessage\":\"Failure 2022-06-22 11:51:07.402 failed DebugManager.processJob, run=859a0c64-d8c1-47ef-9558-448df6c58d21, errorMessage=com.microsoft.dataflow.broker.InvalidOperationException: DSL compilation failed: DF-DSL-001 - DSL stream has parsing errors\nLine 14 Position 114: token recognition error at: '\\n'\nLine 14 Position 181: token recognition error at: '\\n'\"}}\n"
} - RunId: 859a0c64-d8c1-47ef-9558-448df6c58d21

I've already checked this post, but I wasn't able to replicate the given answer. Could you please help me out?

Kind regards,
Francisco.

Answer 1

Hi @Francisco Dominguez ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query.
It looks like you are trying to run a SQL query in dataflow but it's throwing parsing related errors. Please let me know if my understanding is incorrect.

Kindly use the below expression :

"SELECT a.col1, col2, col3, col4 FROM Table1 a, Table2 b WHERE a.col1=b.col1 AND col3 >='"+ $last_year_date +"' and col2 in " + replace(replace(replace($ticker_groups,'[','('),']',')'),'\"','\'')  

I tried to reproduce your scenario and was able to get through it.

1. Here are the two tables -Stores and Items am trying to join based on Store_Id and Item_Store_ID
   
2. Created two parameters : last_year_date and ticker_group
   
3. In the source transformation, used this expression to achieve the above stated requirement: "SELECT a.Store_ID, Store_Name, Item_Id, Item_Name, Item_Price, b.Item_Store_ID FROM dbo.stores a, dbo.items b WHERE a.Store_ID=b.Item_Store_ID and modifieddate>='"+ $last_year_date +"' and Item_Name in " + replace(replace(replace($ticker_group,'[','('),']',')'),'\"','\'')
   
4. Got the expected result:
   

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators