topic Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so? in Data Engineering

I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

Sudeshna — Tue, 15 Mar 2022 18:49:37 GMT

I was trying to create a variable and i got the following error -

command -

SET a = 5;

Error -

Error running query

Configuration a is not available.

Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

User16764241763 — Wed, 16 Mar 2022 16:42:11 GMT

Hello @Sudeshna Bhakat

Thank you for posting on Databricks Community Forum

Could you please use Dashboard filters

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

Query

SELECT l_linenumber AS linenumber, COUNT(*) AS cnt FROM lineitem WHERE l_linenumber = {{var}}

GROUP BY l_linenumber

Query editor looks like below

When you add this query to the dashboard, the var looks like below

Dashboard

Please let us know if this works for you.

I have filed a feature request internally to support this.

Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

Anonymous — Thu, 17 Mar 2022 12:25:03 GMT

Here is an example of the syntax:

set a.variablename = value

Then to use it you need to reference it with ${a.variablename}. See the screenshot

Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

BilalAslamDbrx — Sun, 20 Mar 2022 08:35:27 GMT

@Sudeshna Bhakat what @Joseph Kambourakis described works on clusters but is restricted on Databricks SQL endpoints i.e. only a limited number of SET commands are allowed. I suggest you explore the curly-braces (e.g. {{ my_variable }}) in Databricks SQL.

Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

Sudeshna — Mon, 21 Mar 2022 10:18:49 GMT

Using curly braces means it will create parameters. but i don't need a parameter. There is no way other than creating a parameter?

Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

BilalAslamDbrx — Mon, 21 Mar 2022 10:39:39 GMT

No, not yet. For now, you can only use SET commands to set a limited number of configuration options.

Re: I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

Sudeshna — Mon, 21 Mar 2022 10:41:25 GMT

Got your point. Thank you very much for the help