2

When passing a filter from an Explore like in the below example for whatever reason we cannot get it to provide double single quotes in the query condition.

enter image description here

In the query inside the view has the condition that pulls the submitted value:

As you can see below WHERE {% condition bill_id %} bill_id {% endcondition %} is used to pass the variable.

enter image description here

The resulting query looks like:

enter image description here

This would be fine in a normal query but we have to use OPENQUERY() here due to a compatibility issue with SQL Server and the linked server we are pulling info from. Because we use OPENQUERY we require double quotes to pass variables in OPENQUERYs query string.

Essentially we need the resulting query in the view to look like this:

enter image description here

But no matter what we try to do to add the extra single quotes for some reason it appears that looker is removing them and only using single quotes. like this:

enter image description here

So the question comes down to this:

Does anyone know how to pass a variable to the query in a view from an explore and format it so that it uses double single quotes instead of single single quotes.

We have tried a few things to format this condition to include double single quotes. Since looker uses liquid html we have tried to concatenate with | and we have tried to use append: also.

What can we do to take this:

WHERE {% condition bill_id %} bill_id {% endcondition %}

Resulting in this:

WHERE (bill_id = 'value')

To instead be this:

WHERE (bill_id = ''value'')
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
  • Do you have any control of the actual string resulting in ”WHERE (bill_id = 'value')”? I mean, what do you provide into the bill_id variable? – FinneVirta Nov 25 '22 at 21:49
  • Have you tried using the raw tag? {% raw %} {% this %} {% endraw %} – FinneVirta Nov 25 '22 at 21:51
  • Did you try to escape the inner single quotes with backslashes? `WHERE (bill_id = '\'value\'')` – c_froehlich Nov 25 '22 at 21:57
  • Another solution could be to use the ASCII character for a single quote, CHAR(39). Something along the lines of `WHERE (bill_id = char(39) + str(value) + char(39)` – FinneVirta Nov 25 '22 at 22:39
  • @c_froehlich yes we have tried escaping inner single quotes. – Mike - SMT Nov 26 '22 at 20:10
  • @FinneVirta We will try both of your options when we get to work monday. I am not sure they will work because even adding multiple single quotes hard coded or provided in the variable input LOOKER just reduces it down to single quotes. I suspect its using a REPLACE in its logic somewhere to write the query. – Mike - SMT Nov 26 '22 at 20:11
  • @FinneVirta sadly neither worked for us. – Mike - SMT Nov 30 '22 at 14:12
  • May be try to use double-quoted strings in SQL Server, as here https://stackoverflow.com/a/18647452/1075282 . Like `SET QUOTED_IDENTIFIER OFF; SELECT * FROM OPENQUERY(DBXA, "SELECT * FROM CHG_AUDT WHERE {% condition bill_id %} bill_id {% endcondition %}"); SET QUOTED_IDENTIFIER ON;` – Renat Dec 01 '22 at 23:57
  • @Renat the problem lies before the query ever sends to SQL Server. The problem is in a derived table in the LOOKER view. So the double single quotes must be created on the looker side. – Mike - SMT Dec 02 '22 at 01:13

1 Answers1

1

If you only need to support equality comparisons, you should be able to do this with a liquid parameter, instead of a templated filter. Docs.

view my_view {
  derived_table: {
    sql:
      select * from openquery(DBXA, '
        select *
        from asdf_chg_audt
        where asdf_bill_id = ''{% parameter filtered_bill_id %}''
      '
    ;;
  }
  parameter: filtered_bill_id {
    type: unquoted
  }
}
tconbeer
  • 4,570
  • 1
  • 9
  • 21