How to pass multiple Value to a dynamic Variable in Dashboard underlying SQL

[Linglin]

select         {{user_defined_variable}} as my_var,

                   count(*) as cnt

from            my_table

where         {{user_defined_variable}} = {{value}}

for user_defined_variable, I use query based dropdown list to get a column_name I'd like in the selection. 

for user_defined_value, I can only use text to pass one value

Since the value selection is based on my selection of user_defined_variable, I can't use dropdown list to predefine value. I tried to nest variable in the query used for user_defined_variable. It doesn't work. 

I also tried {{user_defined_variable}} in ({{value}}), doesn't work as well. 

I wonder if anyone knows a solution. thanks!

[Kaniz]

Hi @Linglin, It seems you’re dealing with user-defined variables in your SQL query, and you want to dynamically set both the column name and the value in your WHERE clause.

Let’s break down the solution:

1. Setting User-Defined Variables: You can set a user-defined variable using the := syntax.

   For example:

   SET @my_var := 'column_name';
   

2. Using the Variable in Your Query: To use this variable in your query, you can do something like:

   SELECT @my_var AS my_column, COUNT(*) AS cnt
   FROM my_table
   WHERE @my_var = 'some_value';
   

3. Dynamic Column Names: If you want to dynamically set the column name based on user input, you can use a subquery or a prepared statement.

4. Here’s an example using a subquery:

   SELECT (SELECT column_name FROM information_schema.columns WHERE table_name = 'my_table' AND column_name = @my_var) AS my_column,
          COUNT(*) AS cnt
   FROM my_table
   WHERE @my_var = 'some_value';
   

5. Prepared Statements: Prepared statements allow you to dynamically construct queries. However, they are typically used in stored procedures or application code rather than directly in SQL. Here’s a simplified example:

   SET @sql = CONCAT('SELECT ', @my_var, ' AS my_column, COUNT(*) AS cnt FROM my_table WHERE ', @my_var, ' = ?');
   PREPARE stmt FROM @sql;
   EXECUTE stmt USING 'some_value';
   DEALLOCATE PREPARE stmt;
   

Remember to adapt these examples to your specific database system (e.g., MySQL, PostgreSQL, etc.). Also, ensure proper error handling and security measures when using dynamic column names and values in your queries. 😊👍

For more details, you can refer to the MySQL documentation on user-defined variables.¹²

[Linglin]

Thank you very much for your reply. I'm not sure if my databricks environment is MySQL. SET statement does't work for me. (I tried both dbx notebooks(SQL enviroment, I think it's spark sql) and in sql editor). In SQL editor, it shows PowerBI on the right corner and it supports dashboard rendering. 

For me {{}} works well as variable setter. It worksWHERE {{my_var}} = {{some_value}}/where {{my_var}} in ({{some_value}}) when I pass in one value for {{some_value}} in both statements, but returns error message when I try to return multiple values. I wonder in your statement, if it works for multiple values. 

[YuliyanBogdanov]

It seems to work, try and create all your variables as a Drop Down List, with single value allowed and only the filter i.e. msp in my example as a multiple values allowed, no quotations in the quotation drop down.

Let me know if that helped.