Declare a variable in RedShift

Question

SQL Server has the ability to declare a variable, then call that variable in a query like so:

DECLARE @StartDate date; SET @StartDate = '2015-01-01';  SELECT * FROM Orders WHERE OrderDate >= @StartDate; 

Does this functionality work in Amazon's RedShift? From the documentation, it looks that DECLARE is used solely for cursors. SET looks to be the function I am looking for, but when I attempt to use that, I get an error.

set session StartDate = '2015-01-01';  [Error Code: 500310, SQL State: 42704]  [Amazon](500310) Invalid operation: unrecognized configuration parameter "startdate"; 

Is it possible to do this in RedShift?

Answer 1

Slavik Meltser's answer is great. As a variation on this theme, you can also use a WITH construct:

WITH tmp_variables AS ( SELECT     '2015-01-01'::DATE AS StartDate,     'some string'      AS some_value,    5556::BIGINT       AS some_id )  SELECT * FROM Orders WHERE OrderDate >= (SELECT StartDate FROM tmp_variables); 

Answer 2

Actually, you can simulate a variable using a temporarily table, create one, set data and you are good to go.

Something like this:

CREATE TEMP TABLE tmp_variables AS SELECT     '2015-01-01'::DATE AS StartDate,     'some string'      AS some_value,    5556::BIGINT       AS some_id;  SELECT * FROM Orders WHERE OrderDate >= (SELECT StartDate FROM tmp_variables); 

The temp table will be deleted after the transaction execution.
Temp tables are bound per session (connect), therefor cannot be shared across sessions.