How to overcome missing query parameters in Databricks SQL?

[dimsh]

Hi, there!

I'm trying to build up my first dashboard based on Dataabricks SQL. As far as I can see if you define a query parameter you can't skip it further. I'm looking for any option where I can make my parameter optional.

For instance, I have a table (start_date, end_date, country_code, unique_ids) and want by default to show the data for the last 7 days. But at the same time, I want to add two inputs where a user can specify his / her arbitrary date range.

When I don't provide any value I get a "Missing values for start_date, end_date parameters" error. So if I get a null in this case I can process it via SQL, but my query just fails.

Is there any chance to overcome this limitation?

With BR,

Dmytro

[Hubert-Dudek]

What about widgets?

dbutils.widgets.dropdown("start_date", default_value, values)

dbutils.widgets.dropdown("end_date", default_value, values)

[dimsh]

@Hubert Dudek​ 

Thanks. But I'm using Databricks SQL (not Data Engineering) and I'm only limited with SQL syntax.

https://docs.databricks.com/sql/user/queries/query-parameters.html

[Axel_Schwanke]

not usable in my case

iwtLegacyAnbieterId like '%{{providerId}}%'

I simply want the option of getting all records by leaving the parameter empty

[DavePete_79593]

Also, the mere fact that {{}} exists in the SQL will cause Databricks console to error it. While debugging I had actually moved the parameter {{}} part to a SQL comment -- {{}} and the console errors it even though it's only in a comment so obviously the above won't work.

[Anonymous]

@Dmytro Imshenetskyi​ - Does this last answer help?

[dimsh]

@Kyle Hale​ 

Thanks. It works, but from my perspective, it looks like a workaround.

Probably it will be better to consider empty values as nulls, and then process them via COALESCE SQL function.

[austin_ford]

@Dmytro Imshenetskyi​ When you are building a DBSQL dashboard, you can set the default for parameters so users don't have to select such a parameter: https://docs.databricks.com/sql/user/queries/query-parameters.html#query-parameter-mapping-in-dashbo...

[Anonymous]

@Dmytro Imshenetskyi​ - Checking on you. How have these more recent answers worked out?

[Axel_Schwanke]

any solution for my problem of using a parameter in a 'like' where clause, e.g.

iwtLegacyAnbieterId like '%{{providerId}}%'

and getting ALL records by leaving the parameter empty?

[Anonymous]

@Axel Schwanke​ - Let me see if I can find someone to help you. Thank you for your patience.

[DavePete_79593]

I have a similar problem and none of the above proposals work. I'm trying to do an array_overlap(column_array, user_input_array). The user_input_array should be optional. So I can do something like select case when cardinality(column_array) = 0 or cardinality(user_input_array) > 0 then true else array_overlap(column_array, user_input_array) end as matched. However this won't work because the SQL UI won't allow empty input parameters.

[DavePete_79593]

And I can't submit a feature request because when I select feedback I get: HTTP ERROR 403

Problem accessing /sso/ideas. Reason:

Invalid or missing CSRF token