Setting variables in snowflake

Question

I want to define variables before a CTE table and after a CTE table because some variables are dependent on the result of the CTE table. For example

SET(K,B) = (5,2);

with my_data(Key,Index,Value) as (
    -- data table as cte
    select * from values
        (1, 3, 10),
        (1, 5, 18),
        (1, 14, 4),
        (2, 2, 11),
        (2, 13, 24),
        (2, 29, 40)
)

SELECT VALUE + $K
FROM my_data

This examples works perfectly. But this code:

SET(K,B) = (5,2);

with my_data(Key,Index,Value)   as (
    -- data table as cte
    select * from values
        (1, 3, 10 ),
        (1, 5, 18 ),
        (1, 14, 4 ),
        (2, 2, 11 ),
        (2, 13, 24),
        (2, 29, 40)
)

SET AVG_VAL = (SELECT AVG(VALUE) FROM my_data);

SELECT VALUE + $AVG_VAL
FROM my_data

doesn't because snowflake gives me this error

"SQL compilation error: syntax error line 34 at position 0 unexpected 'SET'."

Should I create a temporary table to store the result of this query (SELECT AVG(VALUE) FROM my_data) in it and then include/use this temporary table for future queries instead of a variable?

user_43992 · Answer

Your "CTE" is not a standalone "thing" it only exist in the context of a SELECT.

Thus

WITH cte_x AS (...)
SELECT * FROM cte_x

is one SELECT which has a CTE attached to it.

Thus for you variable assignment the CTE has to be "IN" the paren's

   with my_data(Key,Index,Value) as (
        select * from values
            (1, 3, 10 ),
            (1, 5, 18 ),
            (1, 14, 4 ),
            (2, 2, 11 ),
            (2, 13, 24),
            (2, 29, 40)
    )
    SELECT AVG(VALUE) FROM my_data;

AVG(VALUE)
17.833333

given that is a discrete chunk of SQL, that can be captured into the variable:

set AVG_VAL = (
    with my_data(Key,Index,Value) as (
        select * from values
            (1, 3, 10 ),
            (1, 5, 18 ),
            (1, 14, 4 ),
            (2, 2, 11 ),
            (2, 13, 24),
            (2, 29, 40)
    )
    SELECT AVG(VALUE) FROM my_data
);

status
Statement executed successfully.

now we can use that value:

select $AVG_VAL * 2;

$AVG_VAL * 2
35.666666

But the next query:

SELECT VALUE + $AVG_VAL
FROM my_data

002003 (42S02): SQL compilation error:

Object 'MY_DATA' does not exist or not authorized.

has no CTE called my_data, so that need to be insert:

with my_data(Key,Index,Value) as (
        select * from values
            (1, 3, 10 ),
            (1, 5, 18 ),
            (1, 14, 4 ),
            (2, 2, 11 ),
            (2, 13, 24),
            (2, 29, 40)
    )
SELECT VALUE + $AVG_VAL
FROM my_data

If you want a table that can be "used twice" you will need an actual table, at which point I would suggest a temporary table so it only have context in this session.

Which the nature of Pankaj's answer (ether via a permanent or temp table)

user_13915313 · Answer

This can be done as in -

select * from d2;
+-----+-----+
| ID1 | ID2 |
|-----+-----|
|   1 |   2 |
| 100 |   2 |
|   3 |   4 |
| 300 |   4 |
+-----+-----+

Setting variable -

set (var1) = (select sum(id2) from d2);
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Using variable -

select id1+$var1 from d2;
+-----------+
| ID1+$VAR1 |
|-----------|
|        13 |
|       112 |
|        15 |
|       312 |
+-----------+

user_5070879 · Answer

An alternatvie approach is to simply use windowed AVG function:

with my_data(Key,Index,Value) as (
    -- data table as cte
    select * from values
        (1, 3, 10),
        (1, 5, 18),
        (1, 14, 4),
        (2, 2, 11),
        (2, 13, 24),
        (2, 29, 40)
)
SELECT VALUE, AVG(VALUE) OVER(),
      VALUE + AVG(VALUE) OVER()
FROM my_data;

Output:

OVER() means that the window used to compute average spans over all rows.

Setting variables in snowflake

3 Answers