Snowflake error: Numeric value 'Track Code' is not recognized

user_18885544 0
Viewed 109

I am trying to find out why I am getting this error and how to fix. I got error: Numeric value 'Track code' is not recognized after union all statement using 3 tables. However, I don't know which column this value belong to. Therefore my first question is how to find out which column has this value 'Track code'. I ran each of those 3 queries below trying to find out if any of those individual step has issues.They ran successfully. However error shows up once I do the union all.

select * from table1 
union all 
select * from table2
union * from table3

Is anyone familiar with this error: Numeric value 'xxx' is not recognized in Snowflake? Thanks in advance!

snowflake-cloud-data-platform
1 Answers

I know how to reproduce the error:

with table1 as (
    select 1 x
), table2 as (
    select 'Track Code' y
), table3 as (
    select 5 z
)

select * from table1
union all 
select * from table2
union all
select * from table3

What's happening is that the first table knows that there is a number on the first column. Then the following tables in the union are expected to have numbers on the first column too.

Solution: Instead of SELECT *, define exactly which columns you are selecting, and make sure they are all of the same type by that order.

user_1324380
Related