CODEWITHSUNDEEP
snowflake-cloud-data-platform
DaMaill
DaMaill

Reputation: 925

Snowflake - Operation in VALUES clause - Invalid expression

How can we get a rational number in a VALUES clause in Snowflake ? 

SELECT * FROM ( 
   VALUES (1/3.0), (2)
) AS  t ;

returns :

SQL compilation error: Invalid expression [1 / 3] in VALUES clause

Upvotes: 3

Views: 5494

Answers (2)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2870

This is so peculiar it could be characterised as a bug.
Any division that results in a whole number works, but fractions trigger the error message.

The Documentation states that:
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.

Clearly (1/3) should be such a simple arithmetic expression. The simple workaround is of course to calculate the answer and include as a decimal number:

SELECT * FROM (VALUES (0.33333333333333333), (2)) AS T(VAL);

On second thought this is not as straight forward as it seems. If those values are used in a CREATE TABLE AS SELECT statement, should the data type be NUMBER(7,6) or FLOAT or something else? Maybe it is best to be specific in these cases.

The specifics for division etc are documented in Scale and Precision in Arithmetic Operations.

Upvotes: 5

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

if you know you have a large number of rational input you want you can just do the math in the select

SELECT column1/column2 FROM VALUES (1,3),(2,1);

giving:

COLUMN1/COLUMN2
0.333333
2.000000

Upvotes: 2

Related Questions