Safe casting VARCHAR to DECIMAL in Teradata

Question

In Teradata DB I have source table

create set table SRC_TABLE (
    Some_Id varchar(2O) not null
);

This table is loaded with data from external system. I have target table

create set table DST_TABLE (
    Some_Id decimal(4,0) not null
);

I need to copy rows from SRC_TABLE to DST_TABLE safely. There is a contract in place that external system will provide only values convertible to DECIMAL(4). However, is there any safe way how to select rows in SRC_TABLE which are not compliant with contract and may cause typecasting failure?

Update: I cannot use UDF functions due to restrictions in environment I am working in.

Answer 1

I would suggest using the MERGE INTO operation in SQL with Error Tables to capture the records that could not be applied. This will allow you to load the data and post-process the records in the error tables that could not be applied.

You could also download the appropriate UDF libraries from the Teradata Developer Exchange and use the IsNumeric() equivalent to perform a conditional check on each row of the SRC_TABLE to avoid inserting non-numeric data into the table. This conditional check could discard the entire record, load the record to a log table, or set the value to an agreed upon default value for invalid data.

CREATE ERROR TABLE MyDB.TGT_TABLE_ERR FOR MyDB.TGT_TABLE; -- Creates Error Table for MERGE INTO operation

MERGE INTO MyDB.TGT_TABLE T1
     USING MyDB.SRC_TABLE T2
        ON T1.{primary index} = T2.{primary index}
WHEN MATCHED THEN
     UPDATE SET Some_ID = CAST(T2.Some_ID AS DECIMAL(4,0))
WHEN NOT MATCHED THEN
     INSERT VALUES (T2.{column list})
LOGGING ALL ERRORS WITH NO LIMIT;