CODEWITHSUNDEEP
snowflake-cloud-data-platform
Lukasz
Lukasz

Reputation: 2606

Setting variables inside a SELECT

I'm in the process of rewriting a mySQL 5.7 query to Snowflake. I would like to keep as much of the mySQL query as close as possible when converting to Snowflake.

PROBLEM: The issue that I'm facing is that I have some user defined variables that change dynamically based on the previous value in a given column and I'm unable to replicate that functionality. I've been able to set variables but when I attempt to change their value as described I'm presented with a series of error.

MySQL Query:

SELECT
    temp.id,
    temp.profile_id,
    temp.citations,
    temp.DataValue,
    @rank := CASE
        WHEN @prevId = temp.profile_id AND @prevDataValue = temp.DataValue THEN @rank+1
        ELSE 1
    END as DataValueRank,
    @prevId := temp.profile_id AS prevId,
    @prevDataValue := temp.DataValue AS prevDataValue
FROM
    l.temp AS temp,
    (SELECT @prevId := 0, @prevDataValue := 0, @rank := 0) AS X
ORDER BY
    profile_id DESC,
    DataValue ASC,
    citations DESC

in the above query we have that @prevId will be the value of the previous value found in the profile_id column, similarly for @prevDataValue and DataValue.

The table that gets created looks like the following:

id          profile_id     citations   DataValue           DataValueRank   prevId       prevDataValue
...
24508771    1003077033      1           E04.936.580.225     49             1003077033   E04.936.580.225
24160975    1003077033      1           E04.987             50             1003077033   E04.987
24160975    1003077033      1           E04.987.775         51             1003077033   E04.987.775
28079605    1003077025      9           C10                  1             1003077025   C10
28079605    1003077025      9           C10.597              2             1003077025   C10.597
...

Where l.temp is the same as above with the last three columns:

id          profile_id     citations    DataValue           
...
24508771    1003077033      1           E04.936.580.225     
24160975    1003077033      1           E04.987             
24160975    1003077033      1           E04.987.775         
28079605    1003077025      9           C10
28079605    1003077025      9           C10.597
...

ATTEMPT

Snowflake Query

SET (prevId, prevDataValue, rank) = (0, 0, 0); -- AS X

I'm thinking that this acts as my variable initializer similar to

SELECT @prevId := 0, @prevDataValue := 0, @rank := 0) AS X

SELECT
    temp.id,
    temp.profile_id,
    temp.citations,
    temp.DataValue,
--    $rank = CASE
--        WHEN $prevId = temp.profile_id AND $prevDataValue = temp.DataValue THEN $rank+1
--        ELSE 1
--    END as DataValueRank, -- THIS WON'T CHANGE VALUE DEPENDING ON THE CASE
--    $prevId = temp.profile_id, -- THIS WON'T CHANGE VALUE
--    $prevDataValue = temp.DataValue -- THIS WON'T CHANGE VALUE
    FROM
        DATAWAREHOUSE.MY_DATA AS temp,
    ORDER BY
        profile_id DESC,
        DataValue ASC,
        citations DESC
;

If anybody knows how the variables can change value within the SELECT statement that would be helpful

Upvotes: 0

Views: 96

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

It's not possible to change values of SQL variables within the SELECT statement. Only possible option is to use SET command to assign/change value of an SQL variable in Snowflake:

https://docs.snowflake.com/en/sql-reference/session-variables.html

I understand that MySQL's variables is very useful, but Snowflake does not support same functionality.

Upvotes: 1

Related Questions