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?
CodePudding user response：
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
CodePudding user response：
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]'));