How to add a select all option in a sql databricks parameter? Or if the parameter value is null make it select all?

user_18582445 0
Viewed 206

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.

aws-databricks databricks sql
2 Answers

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

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

user_12904850

IN clause can't be used with WHERE. You should use =, >, or <.

SELECT * FROM <Table_name> WHERE store > <value> OR store IS NULL;.

Refer WHERE clause for more details.

user_159690410
Related