Identifying which column is throwing error on INSERT

Database

Identifying which column is throwing error on INSERT

SEL REPAIRABLE, 'TRUE'
from T2
WHERE   REGEXP_INSTR(TRIM(REPAIRABLE ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 1
ORDER BY 1 DESC

3535 : A character string failed conversion to a numeric value:

This error has haunted my for days now and I need it resolved ASAP.

I'm doing an INSERT INTO t1 SELECT  COL1, ... COL71 FROM T2, T3, T4 T5 and I get this error.

I've performed the REGEXP_INSTR logic from above again for every numeric type in the select and got the full set returned and 'TRUE' which I interpret to mean that none of the numeric fields had any character values to process.

What would really be helpful is knowing which field was throwing the error?

Any sugesstions?

2 REPLIES
Teradata Employee

Re: Identifying which column is throwing error on INSERT

select *
from YOUR_TABLE
where to_number(trim(COL_N),'9999999999') is null

With this one you can discover about wrong data. Repead this operation with all the columns or include the filter for all the columns and you'll get all wrong data at once

Re: Identifying which column is throwing error on INSERT

Thanks AtardecerR0j0. This helps !!