Snowflake: argument 1 to function SYSTEM$ABORT_TRANSACTION needs to be constant

user_17099684 0
Viewed 149

The snowflake statement:

select system$abort_transaction(id) from values (10) as transactions(id);

Throws an SQL compilation error:

SQL compilation error: argument 1 to function SYSTEM$ABORT_TRANSACTION needs to be constant, found 'TRANSACTIONS.ID'

Is there a workaround or it should be fixed by snowflake devs?

UPD: I should have provided more context earlier. What I want to do to abort all active transactions. Something like that:

show transactions;
select system$abort_transaction("id"::int) from table(RESULT_SCAN (LAST_QUERY_ID()));

UPD2:

The following queries are executed just fine:

show transactions;
select dateadd(millisecond, "id"::int, current_date()) from table(RESULT_SCAN (LAST_QUERY_ID()));
snowflake-cloud-data-platform
2 Answers

The problem is self-explanatory from the error message. Try using a variable as the argument (this works for single ids).

SET v_id = (select "id" from  table(RESULT_SCAN (LAST_QUERY_ID())) as t);

select SYSTEM$ABORT_TRANSACTION($v_id);

You need to either construct it dynamically using a stored procedure if you want to run for multiple values. It can also be automated by generating the SELECT statements. You would then have to use SnowSQL command line like interface to run the generated script.

user_79985910

The problem here seems to be with the parameter being passed to the System Control Function system$abort_transaction(). As mentioned by @NickW in the comments, the parameter that should be passed is the identifier for the transaction to abort. It appears that id in your code is the parameter you passed, whereas this should be a number.

user_10098230
Related