Missing selection for Parameter error

[techg]

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

[Alberto_Umana]

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)

[techg]

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".  

[filipniziol]

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.

[techg]

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