Snowflake Scripting - literals column-CodePudding

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);

CodePudding user response：

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);