Numeric value 'abc_0011O00001y31VpQAI' is not recognized in Snowflake

Question

(Opening the following on behalf of a Snowflake client...)

When I try to insert into the table it threw below error:

Numeric value 'abc_0011O00001y31VpQAI' is not recognized

Have check the table DDL and found only 3 columns defined as NUMBER and rest as VARCHAR.

I checked the SELECT query and didnot find any string value in those NUMBER Datatype columns. Also tried searching in all the Varchar columns for the value 'abc_0011O00001y31VpQAI' , I didn't find any

I know one thing Snowflake doesn't always shows correct error. Am I missing anything here? Is there any way to fix it?

Both COL4_MRR and COL5_QUANTITY are NUMBER

    INSERT INTO TABLE
    (COL1_DATE, COL2_CD, COL3_CUST_NAME, COL3_LOC_NAME,COL4_MRR,COL5_QUANTITY)

    SELECT 
     '2019-10-03' AS COL1_DATE ,
       'AE' AS COL2_CD
       ,CUSTOMER_NAME AS COL3_CUST_NAME
       ,LOCATION_NAME AS COL3_LOC_NAME
       ,MRR_BILLED as COL4_MRR
       ,QTY_BILLED as COL5_QUANTITY
    FROM SCHEMA.V_TABLEA
    union all 
            SELECT 
     '2019-10-03' AS COL1_DATE ,
       'BE' AS COL2_CD
       ,CUSTOMER_NAME AS COL3_CUST_NAME
       ,LOCATION_NAME AS COL3_LOC_NAME
       ,NULL as COL4_MRR
       ,QTY_BILLED as COL5_QUANTITY
    FROM SCHEMA.V_TABLEB

I created a table_D same as original TABLE and tried inserting into it , it worked fine . Then Inserted into Original TABLE from table_D , it worked again .

Deleted those rows from original TABLE and reran the job , it worked fine.

There was no issue with data as all was Number only, I even tried with TRY_TO_NUMBER too. It inserted the data without any changes to the code.

...............

Client is currently waiting on a next day run to re-test to determine if this is either a bug or an issue with their data. In the meantime, we are interested to see if anyone else has run into similar challenges and have a viable recommendation. THANK YOU.

@Suzy Lockwood I am having same issue during union all in snowflake. I have same structure as Gavin's and it is giving me error while I'am selecting varchar column. the problem is occurring during 'UNION ALL'. As you can see in Gavin's first select statement he is providing column name 'MRR_BILLED' and in second select statement he is putting 'NULL' instead of column name to match an order. I am doing same because I doesn't have value for second select statement and it is giving me value error. Kindly give me a solution how to tackle this. Thank you! — patel94

Suzy Lockwood Suzy Lockwood · Accepted Answer · 2019-10-08T22:27:35

The error typically means you are trying to insert non-numeric data (like 'abc_0011O00001y31VpQAI') into a numeric column. It seems like the customer did everything right in testing and TRY_TO_NUMBER() is a great way to verify numeric data.

Do the SELECT queries run fine separately? If so, then I would check whether there might be a potential mismatch in the datatype of the columns and make sure they are in the right order.

I would also check whether or not the header is being skipped in the file (that may be where the 'abc_0011O00001y31VpQAI' is coming from since the customer did not see it in the data).

Numeric value 'abc_0011O00001y31VpQAI' is not recognized in Snowflake

2 Answers