Cannot inner join two simple CTEs on a field with mixed data types in Snowflake

Question

Problem

I cannot inner join two simple CTEs in Snowflake. Terse error message does not show the way. What is wrong with my query?
Bonus points: Why does my query work in SQL Server but not Snowflake?

Background

I am new to Snowflake. I am used to SQL Server. I want to query an inner join of 2 tables in Snowflake. Tables 1 and 2 show the tables that I want to join. Table 3 is the result that I want.

There are some rows with the first table that I want to remove inside a CTE with a simple where clause. When I run my query (see below), I get a verse terse error message:

Numeric value 'HAHA! IM CAUSING TROUBLE' is not recognized

But I thought I "removed" this value with my first CTE.

~~What is wrong with my query? Bonus points: Why does my query work in SQL Server but not Snowflake?~~

Table 1: Field History

id	date	field_id	field_value
1	2020-01-01	unwanted	HAHA! IM CAUSING TROUBLE
2	2020-01-02	thing	100
3	2020-01-03	thing	101
4	2020-01-04	thing	102
5	2020-01-05	thing	null
6	2020-01-06	thing	103

Table 2: Things I Want to Join

thing_id	thing_start_date	thing_end_date	something_i_care_about
100	2020-01-01	2020-02-01	secret alien intelligence
101	2020-02-01	2020-03-01	blueprints for shark lazers
102	2020-03-01	2020-04-01	non-YA biz-NAZZ
103	2020-04-01	2020-05-01	who will win bachelorette

Table 3: Final Table of My Dreams

id	date	thing_id	thing_start_date	thing_end_date	something_i_care_about
2	2020-01-02	100	2020-01-01	2020-02-01	secret alien intelligence
3	2020-01-03	101	2020-02-01	2020-03-01	blueprints for time machine
4	2020-01-04	102	2020-03-01	2020-04-01	non-YA biz-NAZZ
6	2020-01-06	103	2020-04-01	2020-05-01	who will win bachelorette

What I have Tried

with field_history as ( -- CTE with simple where clause

  select
      id
      , date
      , to_number(field_value, 38, 0) as thing_id  -- SQL Server equivalent would be cast() or convert()
  from db.schema.history
  where field_id = 'thing' and field_value is not null

),

things_i_want as (

  select
    *
  from db.schema.things

),

final as (
  
  select
    field_history.id
    , field_history.date
    , things.*
  from field_history
  inner join things_i_want on field_history.thing_id = things_i_want.thing_id

)

select * from final

Super Helpful Error Message Blocking me from My Dreams

Numeric value 'HAHA! IM CAUSING TROUBLE' is not recognized

user_1144035 · Answer

Numeric value 'HAHA! IM CAUSING TROUBLE' is not recognized

Your error message is a type conversion problem and would appear to be here:

to_number(field_value, 38, 0) as thing_id

You may think that the where clause filters out the bad values. However, SQL engines can -- and do -- rearrange operations. I would suggest using a case expression to handle this:

(case when field_value regexp '^[0-9]+$'
      then to_number(field_value, 38, 0)
 end) as thing_id

The case expression is guaranteed to run the expressions sequentially.

The above idea (but not the regexp part) works in SQL Server and Snowflake.

In Snowflake only, you can use try_ functions:

try_to_number(field_value, 38, 0) as thing_id

user_15368278 · Answer

It is simple error. You are trying to convert a string 'HAHA! IM CAUSING TROUBLE' to Number. Even if you remove it, you may have future error because of null in the same column in Field_History table. You need to handle that in query. Not sure exactly what query you wrote in SQL Server so can't say why it worked.