Looker LOOKML will not accept double quotes for passed condition

MikeM1
Participant I

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.
 

2700653a-1bd7-44d5-94fd-8c2da7efd417.png


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.

305999e1-baff-456e-a92d-1fe288de1814.png

The resulting query looks like:

51a80b6e-5237-4a40-888a-f1f8f617fa9c.png

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:

1923c867-cb28-440c-b1a7-d25f920e5b2b.png

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:

df26074a-f056-4e67-9853-227098f018ba.png

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'')
Solved Solved
1 1 708
1 ACCEPTED SOLUTION

MikeM1
Participant I

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 parame...
 

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.

View solution in original post

1 REPLY 1

MikeM1
Participant I

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 parame...
 

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.

Top Labels in this Space
Top Solution Authors