CODEWITHSUNDEEP
databricksazure-databricksdatabricks-sqldatabricks-connectdatabricks-community-edition
Rita Kumari
Rita Kumari

Reputation: 53

How to declare variables in Databricks SQL editor

In Databricks Notebook (Sql) I was able to declare a variable and use it also with below syntax:

set name.table=(select distinct name from t1);

select * from t2 where name IN ${name.table}

but the same is not working in the SQL Editor. Getting error as

"Error running query: [_LEGACY_ERROR_TEMP_DBR_0222] org.apache.spark.sql.AnalysisException: Configuration name is not available."

Need help to know how to declare a variable in Databricks SQL Editor. and also if we need to declare multiple variables then what to be done?

e.g., Name variable, Month, and Year variable

select * from t2 where name IN (Namevariable) 
  and Month IN (MonthVariable) and Year IN (YearVariable)

I have tried everything from the other posts but nothing worked.

Upvotes: 2

Views: 3442

Answers (1)

Herivelton Andreassa
Herivelton Andreassa

Reputation: 426

I assume you can get a similar result by using query parameters (link to the docs https://docs.databricks.com/sql/user/queries/query-parameters.html#query-based-dropdown-list).

Here are some general steps (not executed here) that could achieve that.

  • Create and save your initial query:
select distinct name from t1
  • Inside a new query, add the parameter by clicking in the {} icon and name it as name_parameter. When specifying the parameter, use the following:
    • Click Query Based Dropdown list under Type in the settings panel.
    • Click the Query field and select the first query.

Your initial query will now use the parameter and should like this:

select * from t2 where name IN ( {{ name_parameter }} )

Regarding the usage of multiple parameters, the documentation say it's possible and provides some examples: https://docs.databricks.com/sql/user/queries/query-parameters.html#can-i-use-multiple-parameters-in-a-single-query

Upvotes: 0

Related Questions