我想在查询的顶部设置一些参数/变量,并在Heroku数据剪辑的复杂查询中的多个位置使用它们。下面是一个简单的示例:
WITH vars AS ( SELECT
'2018-01-07' AS calcdate,
12345 AS salary
)
select *
from taxes
where country_alpha3='USA' and year='2018' and active=true
and subdivision_code='US-MEDI'
and local_code is null
and start_date <= DATE(vars.calcdate) and end_date >= DATE(vars.calcdate)
and lower_amount_cents <= vars.salary and upper_amount_cents >= vars.salary;
我在另一个答案(从 2013 年开始(中看到这种风格的代码,截至今天,它在实际的数据剪辑中不起作用。
Error: Your query couldn't be updated.
ERROR: missing FROM-clause entry for table "vars"
LINE 12: and start_date <= DATE(vars.calcdate) and end_date >= DATE(v...
^
如果将vars.calcdate
和vars.salary
更改为常量,则 SQL 工作正常。我认为这与vars.
语法或用法有关。
我找到了解决方案。正如我所想的那样,这是语法。不允许使用 vars.name 格式(不确定我在哪里看到的(。
WITH vars AS ( SELECT
DATE('2018-01-07') AS calcdate,
12345 AS salary
)
select *
from taxes
where country_alpha3='USA' and year='2018' and active=true
and subdivision_code='US-MEDI'
and local_code is null
and start_date <= (SELECT calcdate from vars) and end_date >= (SELECT calcdate from vars)
and lower_amount_cents <= (SELECT salary from vars) and upper_amount_cents >= (SELECT salary from vars);