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