Using "SET" variable at the top of the query

Hey!
We work with a Snoflake DB. When running queries in workbench (/others) we use this syntax a lot:

set LAST_DAYS = (select DATE_PART('EPOCH_MILLISECOND', TO_TIMESTAMP(current_timestamp)) / 1000 - 60 * 60 * 24 * 10);

select * from table

where  table.TIMESTAMP> $last_days

is this doable in REDASH?
I run the top row separately - statement executes successfully.
Then I try to run the rest of the query, but redash says " SQL compilation error: error line 3 at position 26 Session variable ‘$LAST_DAYS’ does not exist"

(the same sequance does work with this DB in other tools - workbench/data-grip etc)

Together with the first part? I.e. just hit execute on the whole query?

tried it different ways.

originally I tried executing the SET line, and then the rest of the query (separately) - that’s how I usually do it in datagrip. I get an the error

SQL compilation error: error line 2 at position 25 Session variable ‘$LAST_DAYS’ does not exist

if I try to run the SET line, and then run the whole query (both parts) I get

001003 (42000): SQL compilation error: syntax error line 3 at position 0 unexpected ‘select’.

if i add a “;” after the SET line, I get

Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

Thanks, this was helpful! So what happens is that tools like Datagrip run all your queries in the same session. But Redash executes each query in its own session, so this explains why option 1 failed.

Option 2 fails, because you need to split your statements with a ;. And option 3 fails because of limitations of their API. We could split the queries into multiple ones and send them one by one in the same session, but I’m not sure if we have a reliable way to do this :thinking:

1 Like

Thanks for that!
can you elaborate on “And option 3 fails because of limitations of their API.” ?

I’ll give smoe context to our use case:
we save all date/time info in tables in UNIX timestamp format. when running a query to get yesterday’s data (for example), I need to use a filters such as:

timestamp > date_part(epoch_seconds, current_timestamp) - 606024*1

which means that the date function needs to run N times per the number of records (=number of timestamp values).
we use the SET function to optimise this , and pre-set values of timestamp-limits (so when the main query is running, we only compare timestamp to timestamp)

any ideas how to achieve this in redash?

*I have an idea that can work (i guess) - use a separate query with

select date_part(epoch_seconds, current_timestamp) - 606024*1 as min_time

and call that query in a parameter in the main query.
I guess this would achieve the same result?
problems with this-
1.need to always work with 2 queries - bit annoying.
2.use of query result in parameter is only by using drop down… not really a blocker, but it’s a “weird” application.

@arikfr BTW - I guess what would really solve all my problems here is:
if I could run SQL in the parameter . or in other words it’d be a query window inside a query window.