Search code examples
variablessnowflake-cloud-data-platformliterals

Snowflake Scripting - literals column

I am try to run following SQL statement passing in variable as participant_key:

select nvl(max(participant_key),0) from table('DEV_CDZ.WDP.PARTICIPANT_INFO')

I am trying to do something like this but is not working. I get

Uncaught exception of type 'STATEMENT_ERROR' on line 47 at position 2 : Numeric value 'PARTICIPANT_KEY' is not recognized

select nvl(max(:v_dest_key),0) into :count_key from table(:v_dest_table);
Solution

  • To use variable as identifier, IDENTIFIER function must be used:

    select nvl(max(IDENTIFIER(:v_dest_key)),0) 
into :count_key 
from table(:v_dest_table);

    An alternative to entire nvl expression could be:

    SELECT ZEROIFNULL(IDENTIFIER(:v_dest_key))
INTO  :count_key 
FROM TABLE(:v_dest_table);

    If column possibly contains text, then is should be first casted to number:

    SELECT ZEROIFNULL(TRY_CAST(IDENTIFIER(:v_dest_key) AS INT))
INTO  :count_key 
FROM TABLE(:v_dest_table);