- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ05-16-2022 04:31 PM
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-para...
- Labels:
-
Databricks SQL
-
DBSQL
-
Parameter
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ05-16-2022 04:34 PM
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 ๐
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ05-16-2022 04:34 PM
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 ๐
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ05-18-2022 09:09 AM
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.