Solved

Looker LOOKML will not accept double quotes for passed condition

  • 17 November 2022
  • 1 reply
  • 67 views

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.
 


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.

 

 

 

The resulting query looks like:

 

 

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:

 

 

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:

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'')

 

icon

Best answer by MikeM 5 December 2022, 20:31

View original

1 reply

We had a post on SO also and a user over there provided a solution.

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
}
}

This fixed the issue for us so for completion I have provided the answer here so others may find it useful.

Reply