CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Woody1193
Woody1193

Reputation: 7980

Sending ARRAY to VALUES clause fails

If I want to construct a temporary valueset for testing, I can do something like this:

SELECT * FROM (VALUES (97.99), (98.01), (99.00))

which will result in this:

COLUMN1
1 97.99
2 98.01
3 99.00

However, if I want to construct a result set where one of the columns contains an ARRAY, like this:

SELECT * FROM (VALUES (97.99, [14, 37]), (98.01, []), (99.00, [14]))

I would expect this:

COLUMN1 COLUMN2
1 97.99 [14, 37]
2 98.01 []
3 99.00 [14]

but I actually get the following error:

Invalid expression [ARRAY_CONSTRUCT(14, 37)] in VALUES clause

I don't see anything in the documentation for the VALUES clause that explains why this is invalid. What am I doing wrong here and how can I generate a result set with an ARRAY column?

Upvotes: 2

Views: 590

Answers (3)

Rajat
Rajat

Reputation: 5803

From the documentation

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.

The documentation doesn't explicitly says this, but given the ability of arrays to hold multiple data types and varying number of elements, I want to say arrays in most SQL based databases are dynamic arrays that don't evaluate at compile time. Maybe some experts can shed more light on this.

Back to your problem, I would just use explicit select statements like:

 select 97.99, [14, 37] union all
 select 98.01, [];

Upvotes: 2

Greg Pavlik
Greg Pavlik

Reputation: 11046

I think the values clause only allows primitive types. You can define it as a string in single quotes and use parse_json to turn it into an array:

SELECT $1 COL1, parse_json($2)::array COL2 
FROM (VALUES (97.99, '[14, 37]'), (98.01, '[]'), (99.00, '[14]'));

Upvotes: 2

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

VALUES() has some restrictions:

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.

https://docs.snowflake.com/en/sql-reference/constructs/values.html

Upvotes: 2

Related Questions