liquidlookerlookml

Looker will not accept double single quotes for passed condition

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

  • I wanted to come back and update this post with something I leaned directly from a Looker rep.

    We figured out that if we wanted to be able to maintain the double single quotes we needed from a field the unquoted portion does not work for stuff like spaces between words. This requires a string field. So to be able to pull in everything no matter how its formatted we can use the following View logic.

    If you apply {{ "'" }} before and after your parameter {% parameter value %} then Looker wont remove the 2nd set of single quotes thus we now have double single quotes since the parameter field applies a single quote also. This is really only needed in OpenQuery that I have discovered so far. If you happen to find a 2nd use case please let me know as I am interested to see where else this might apply.

    Input: Test Value

    Output ''Test Value''

    view: view_name_for_model {
  derived_table: {
    sql: SELECT * FROM OPENQUERY(SERVER_NAME, '
            SELECT * FROM TABLE_NAME
            
            WHERESEARCH_VALUE= {{ "'" }}{% parameter value %}{{ "'" }} ') ;;
  }

  parameter: value {
    type: string
    label: "Search By Value"
  }