Declaring parameters - SQL options

[elgeo]

Hello. Following an older question SQL Declare Variable equivalent in databricks, we managed to find through the following article Converting Stored Procedures to Databricks | by Ryan Chynoweth | Dec, 2022 | Medium, a way to declaring more complicated parameters:

SET var.total_qty = (

SELECT sum(qty)

FROM my_schema.my_staging_source_table

WHERE YEAR(ModifiedDate) = ${var.year_variable} and CostCenter = $CostCenter;

);

However the above is not executed until there is an action - that means it is passed as a nested query in the basic sql stement that follows ( ${var.total_qty} ). Is there any other way to have the result set in a parameter and not be executed for each row in the basic sql? I am interested only on sql options and not python.

Thank you in advance!

[Anonymous · Accepted solution]

@ELENI GEORGOUSI​ : Can you try using a common table expression (CTE) something like below?

WITH cte_total_qty AS (
  SELECT SUM(qty) AS total_qty
  FROM my_schema.my_staging_source_table
  WHERE YEAR(ModifiedDate) = ${var.year_variable} AND CostCenter = $CostCenter
)
 
Select col1, col2,...
from....

Note that the CTE is executed only once, before the main SQL statement, and the result is reused for each row in the main SQL statement. Hope this helps!

[Anonymous]

Hi @ELENI GEORGOUSI​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!