- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-15-2023 04:50 AM
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!
- Labels:
-
DECLARE
-
Nested
-
Parameters
-
SQL
Accepted Solutions
![](/skins/images/1C7D039E274DA4E433FB1B1A3EAE173A/responsive_peak/images/icon_anonymous_profile.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-08-2023 06:00 PM
@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!
![](/skins/images/1C7D039E274DA4E433FB1B1A3EAE173A/responsive_peak/images/icon_anonymous_profile.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-08-2023 06:00 PM
@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!
![](/skins/images/1C7D039E274DA4E433FB1B1A3EAE173A/responsive_peak/images/icon_anonymous_profile.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-10-2023 06:00 PM
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!
![](/skins/images/B38AF44D4BD6CE643D2A527BE673CCF6/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/B38AF44D4BD6CE643D2A527BE673CCF6/responsive_peak/images/icon_anonymous_message.png)