topic Re: How to add a select all option in a Databricks SQL parameter? I would like to use a query-based drop-down list. in Data Engineering

How to add a select all option in a Databricks SQL parameter? I would like to use a query-based drop-down list.

StephanieAlba — Mon, 16 May 2022 23:31:05 GMT

So I want to create a select all button in a parameter. The actual parameter has around 200 options because of the size of the database. However, if I want a general summary where you can see all the options I would have to select one by one and that is not efficient. Any ideas?

Select
*
from Table
where store in ({{store}}) or ({{store}}) is null

I tried this but it didn´t let me use the parameter as null. Also, it is important to mention that the parameter uses values from another query where the distinct names of the stores are listed.

As shown here: https://stackoverflow.com/questions/71622404/how-to-add-a-select-all-option-in-a-sql-databricks-parameter-or-if-the-paramete

Re: How to add a select all option in a Databricks SQL parameter? I would like to use a query-based drop-down list.

StephanieAlba — Mon, 16 May 2022 23:34:36 GMT

You could add '--- All Stores ---' to your list. Here is the query I would use to populate the drop-down. S.O. answer here

SELECT
    store as store_name
FROM
    (
    Select Distinct
      store
    From 
      Table
 
    UNION ALL
 
    SELECT
      '--- All Stores ---' AS store
    )
ORDER BY
  store_name

Next the query that uses the drop down

Select
  *
FROM 
  Table
WHERE 
  (store in ({{store_name}}) or '{{store_name}}' LIKE '--- All Stores ---')

Now if you select '--- All Stores ---' you should get all of the stores 🙂

Re: How to add a select all option in a Databricks SQL parameter? I would like to use a query-based drop-down list.

susodapop — Wed, 18 May 2022 16:09:25 GMT

This is the right approach. You can avoid mixing the `IN` and `LIKE` syntax in this way for your second query:

SELECT * FROM table WHERE
('--- All Stores ---'in {{ PARAM }} or store_name in {{ PARAM }})

Then configure `PARAM` to be a multi-select parameter.

I would also reorder your queries in the backing parameter query so that `--- All Stores ---` always appears at the top of the list, rather than the bottom.