I am moving a query from SQL Server to Snowflake. Part of the query creates a pivot table. The pivot table part works fine (I have run it in isolation, and it pulls numbers I expect).

However, the following parts of the query rely on the pivot table- and those parts fail. Some of the fields return as a string-type. I believe that the problem is Snowflake is having issues converting string data to numeric data. I have tried CAST, TRY_TO_DOUBLE/NUMBER, but these just pull up 0.

I will put the code down below, and I appreciate any insight as to what I can do!

CREATE OR REPLACE TEMP TABLE ATTR_PIVOT_MONTHLY_RATES AS (  
    SELECT  
        Market,  
        Coverage_Mo,  
        ZEROIFNULL(TRY_TO_DOUBLE('Starting Membership')) AS Starting_Membership,  
        ZEROIFNULL(TRY_TO_DOUBLE('Member Adds')) AS Member_Adds,  
        ZEROIFNULL(TRY_TO_DOUBLE('Member Attrition')) AS Member_Attrition,   
        ((ZEROIFNULL(CAST('Starting Membership' AS FLOAT))
              ZEROIFNULL(CAST('Member Adds' AS FLOAT))
              ZEROIFNULL(CAST('Member Attrition' AS FLOAT)))-ZEROIFNULL(CAST('Starting Membership' AS FLOAT)))
                /ZEROIFNULL(CAST('Starting Membership' AS FLOAT)) AS "% Change"
    FROM
        (SELECT * FROM ATTR_PIVOT
            WHERE 'Starting Membership' IS NOT NULL) PT)

I realize this is a VERY big question with a lot of moving parts... So my main question is: How can I successfully change the data type to numeric value, so that hopefully the formulas work in the second half of the query?

Thank you so much for reading through it all!

EDITED FOR SHORTENING THE QUERY WITH UNNEEDED SYNTAX

CAST(), TRY_TO_DOUBLE(), TRY_TO_NUMBER(). I have also put the fields (Starting Membership, Member Adds) in single and double quotation marks.

CodePudding user response：

Unless you are quoting your field names in this post just to highlight them for some reason, the way you've written this query would indicate that you are trying to cast a string value to a number.

For example:

ZEROIFNULL(TRY_TO_DOUBLE('Starting Membership'))

This is simply trying to cast a string literal value of Starting Membership to a double. This will always be NULL. And then your ZEROIFNULL() function is turning your NULL into a 0 (zero).

Without seeing the rest of your query that defines the column names, I can't provide you with a correction, but try using field names, not quoted string values, in your query and see if that gives you what you need.

CodePudding user response：

You first mistake is all your single quoted columns names are being treated as strings/text/char

example your inner select:

with ATTR_PIVOT(id, studentname) as (
    select * from values
    (1, 'student_a'),
    (1, 'student_b'),
    (1, 'student_c'),
    (2, 'student_z'),
    (2, 'student_a')
)
SELECT * 
FROM ATTR_PIVOT
WHERE 'Starting Membership' IS NOT NULL

there is no "starting membership" column and we get all the rows..

So you need to change 'Starting Membership' -> "Starting Membership" etc,etc,etc

As Mike mentioned, the 0 results is because the TRY_TO_DOUBLE always fails, and thus the null is always turned to zero.

now, with real "string" values, in real named columns:

with ATTR_PIVOT(Market, Coverage_Mo, "Starting Membership", "Member Adds", "Member Attrition") as (
    select * from values
    (1, 10 ,'student_a', '23', '150' )
)
SELECT   
    Market,  
    Coverage_Mo,  
    ZEROIFNULL(TRY_TO_DOUBLE("Starting Membership")) AS Starting_Membership,  
    ZEROIFNULL(TRY_TO_DOUBLE("Member Adds")) AS Member_Adds,  
    ZEROIFNULL(TRY_TO_DOUBLE("Member Attrition")) AS Member_Attrition   
FROM ATTR_PIVOT
WHERE "Starting Membership" IS NOT NULL

we get what we would expect: