Numeric value is not recognized

Question

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))
                    

Answer 1

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.

Answer 2

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.