topic Re: Missing selection for Parameter error in Data Engineering

Missing selection for Parameter error

techg — Mon, 02 Dec 2024 10:22:28 GMT

Hi All,

I have created three parameters in an SQL query in Databricks. If no value is entered for a parameter, I would like the query to retrieve all values for that particular column. Currently, I'm getting an error message: "Missing selection for Parameter." How can I resolve this?

select * from table

where country=:country

period=:period

Doc=:Document

If no value is entered for the 'Document' parameter, the query should retrieve all documents; otherwise, it should retrieve the value specified in the parameter

Re: Missing selection for Parameter error

Alberto_Umana — Mon, 02 Dec 2024 13:40:49 GMT

Hello @techg,

Could you try with below query?

SELECT * FROM table
WHERE (:country IS NULL OR country = :country)
AND (:period IS NULL OR period = :period)
AND (:Document IS NULL OR Doc = :Document)

Re: Missing selection for Parameter error

techg — Tue, 03 Dec 2024 07:13:06 GMT

Hi Alberto,

I have tried the above mentioned changes in the SQL query, but I'm still getting the same error "Missing Selection for Parameter error".

Re: Missing selection for Parameter error

filipniziol — Tue, 03 Dec 2024 08:18:33 GMT

Hi @techg ,

For all the 3 parameters you need to create widgets.
The default should be the empty string

CREATE WIDGET TEXT country DEFAULT ''; CREATE WIDGET TEXT period DEFAULT ''; CREATE WIDGET TEXT Document DEFAULT '';

You assign 2 values for country and period, leaving Document an empty string:

The query:

SELECT * FROM sample_table WHERE ( :country IS NULL OR :country = '' OR country = :country ) AND ( :period IS NULL OR :period = '' OR period = :period ) AND ( :Document IS NULL OR :Document = '' OR Doc = :Document );

Sample data:

-- Create a sample table CREATE OR REPLACE TABLE sample_table ( country STRING, period STRING, Doc STRING, value INT ); -- Insert sample data INSERT INTO sample_table VALUES ('USA', '2021', 'Doc1', 100), ('USA', '2022', 'Doc2', 200), ('Canada', '2021', 'Doc3', 150), ('Canada', '2022', 'Doc4', 250), ('Mexico', '2021', 'Doc5', 300);

The result of query on sample data:

The error message suggest that even though you the Document parameter is expected in the query, it is not not defined in your code. Make sure to define the parameter before using it.

Re: Missing selection for Parameter error

techg — Tue, 03 Dec 2024 12:39:47 GMT

I'm creating this query with parameters in SQL Editor in Databricks and added it to the SQL Dashboard.

Do we need to create Widget while creating parameters in SQL Editor?

When i tried creating widget in SQL editor, Im getting syntax error near Widget SQLSTATE. Please advise