Hi @Oliver Teng, Since the date column is not part of any table in the query, the condition WHERE date > ${startDate}will result in an error.
To fix this, you can either remove the subquery and directly specify the value of '2021-01-01'
in the WHERE clause:
SET startDate='2020-01-01';
CREATE OR REFRESH LIVE TABLE filtered
AS SELECT * FROM (SELECT '2021-01-01' as date)
WHERE '2021-01-01' > ${startDate};
Or you can modify the subquery to include the date column in a table:
SET startDate='2020-01-01';
CREATE OR REFRESH LIVE TABLE filtered
AS SELECT * FROM (
SELECT ’2021-01-01’ as date
FROM (SELECT 1) -- Add a ***** table to create a single-row subquery
) t
WHERE date > ${startDate};
In this example, we add a ***** table with a single row to the subquery to create a table alias t with a single column date. We can then reference the date in the WHERE clause.