CODEWITHSUNDEEP
snowflake-cloud-data-platform
orellabac
orellabac

Reputation: 2645

Problem in snowflake when using expression on values clause for an INSERT statement

Hi I am having this problem with Snowflake. I have some insert statement like these inside an Snowflake SP:

DROP TABLE TABLE1;

CREATE table TABLE1 (COLUMN1 VARCHAR(10));

insert into table1 values('TEST' || TRIM(0));

When I execute those statements I get an error like this: SQL compilation error: Invalid expression ['TEST' || '0'] in VALUES clause

Is there a documented limitation for the VALUES clause in snowflake. Any kind of work around for this?

Upvotes: 1

Views: 2011

Answers (2)

orellabac
orellabac

Reputation: 2645

I found a workaround for the limitation that @mike-walton described. You can chance:

insert into table1 values('TEST' || TRIM(0));

to

insert into table1 select 'TEST' || TRIM(0);

And it will work

Upvotes: 1

Mike Walton
Mike Walton

Reputation: 7339

Per the documentation here: https://docs.snowflake.com/en/sql-reference/constructs/values.html

Each expression must be a constant, or an expression that can be evaluated as a constant during compilation of the SQL statement.

Most simple arithmetic expressions and string functions can be evaluated at compile time, but most other expressions cannot.

In looking at your example, you are using a TRIM on a numeric expression, which isn't necessary. If you remove the TRIM(), then your example works fine.

Upvotes: 1

Related Questions