BreakPhreak BreakPhreak - 5 months ago 17
SQL Question

Is there a way to define a named constant/parameter in a single-statement SQL query?

Tried to do something like:

WITH
dates as (SELECT '2015-01-01' as start, '2016-01-01' as end)
SELECT * FROM my_table WHERE start_date >= dates.start AND end_date <= dates.end


But got the error message "Relation 'dates' does not exist" (in Vertica). Is there any proper way to define a constant/parameter. In real example the query contains multiple selects over a defined time range, hence I'd like to maintain the values constants/parameters in a single place to allow them to be reused in the nested subqueries.

IF possible, I'd like to refrain from DECLARE/SET-like statements, where a separate line is required.

Answer

You need to have dates in the FROM clause if you want it in the query. You can do this as:

WITH dates as (SELECT '2015-01-01' as start, '2016-01-01' as end)
SELECT t.*
FROM my_table t JOIN
     dates d
     ON t.start_date >= d.start AND t.end_date <= d.end;

Note: You can also do this with a CROSS JOIN. I often write queries as:

WITH params as (
     SELECT '2015-01-01' as start, '2016-01-01' as end
    )
SELECT t.*
FROM params CROSS JOIN
     my_table t
WHERE t.start_date >= params.start AND t.end_date <= params.end;