I am running into an issue when using direct query with Databricks SQL Warehouses using the Azure Databricks Connector. I have text fields with single quote characthers in the string that need to be rendered in the report. When filtering for a value with an apostraphe the query returns null. Looking at the query history in databricks it looks like the SQL query being generated is using a second single quote to escape the apostraphe, but this is not the escape charachter in Databricks SQL.
Unless the string is prefixed with r, use \ to escape special characters (e.g. ' or \).
If the string is prefixed with r there is no escape character.
Here is documentation for string-type in Databricks SQL.
select
sum(`sales_mlc_amt`) as `C1`
from
(
select
`OTBL`.`sales_amt`
from
`hive_metastore`.`database`.`table` as `OTBL`
left outer join `hive_metastore`.`table`.`market_dim` as `ITBL` on (`OTBL`.`mkt_skid` = `ITBL`.`mkt_skid`)
where
`ITBL`.`mkt_name` = 'Market''s Territory'
) as `ITBL`
Thanks!
on:
URL is not filtering on field parameter