CODEWITHSUNDEEP
snowflake-cloud-data-platform
ajcoder
ajcoder

Reputation: 237

Numeric value is not recognized

Trying to execute a query like this and getting the numeric value not recognized error. The datatypes for the columns used are

Col1 - VARCHAR, 
Col2 - VARCHAR, 
Col3 - TIMESTAMP_NTZ(9)

This is my query

Select count( distinct ( 
                     case when Col1 > 100 
                     then concat(col2,cast(col3 as string))
                     else 0
end))

Upvotes: 6

Views: 62878

Answers (3)

JayRizzo
JayRizzo

Reputation: 3616

To get around the error:

Numeric value 'neverNull' is not recognized

The error is complaining about an empty string, being inside of a numeric column or trying to insert.

To get around this, Try:

SELECT TRY_TO_NUMBER(A.NUMERIC_FIELD_TYPE::VARCHAR) AS FIELD_TEST
FROM ABC AS A

Upvotes: 0

derpirscher
derpirscher

Reputation: 17372

your Col1 is a VARCHAR, yet you are comparing it to a number. While snowflake does have an optimistic automatic type conversion (ie it will convert the string in Col1 to a number), it might fail for one or more of the values. And as you don't catch these errors, your query will fail as a whole.

Select count( distinct ( 
                     case when try_to_number(Col1) > 100 
                     then concat(col2,cast(col3 as string))
                     else '0'
end))

try_no_number will try to convert the values of Col1 to a number and return null when it fails. But it won't throw an error.

And also take into account what Simeon said. Ie the result of your CASE has different types. I don't know, how DISTINCT handles this, but I can imagine, that it decuces the type from the first value it sees, and if that is a number, it might try to convert all other elements to numbers as well, which can --depending on the values -- lead to the same error.

Upvotes: 2

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

your CASE is returning a string and a number which are not the same types, so if you make the 0 into a string '0' that should work better.

A side note, you will still have a count of 1 for the all the values <= 100, which seems somewhat strange. Where-as if you used null instead of the 0 then it will not count, and will not have a type problem.

Upvotes: 4

Related Questions