Heroku dataclip variable substitution in query

Question

I want to set some parameters/variables at the top of the query and use them in several places in a complex query on Heroku Dataclips. Here is a simple example:

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;

I saw this style of code in another answer (from 2013) and it is not working in the actual dataclip as of today.

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...
                                 ^

If the vars.calcdate and vars.salaryare changed to the constants, then the SQL works fine. It is something to do with the vars. syntax or usage, I think.

Answer 1

I found the solution. It was syntax as I supposed. The format of vars.name is not allowed (not certain where I saw that).

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);