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.

use trycast() https://stackoverflow.com/questions/39386736/convert-char-to-int-teradata-sql — jim, Apr 20 '18 at 15:07

Rob Paller · Answer 1 · 2012-04-19T13:27:33.543

0

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;

edited Apr 19 '12 at 13:27

answered Apr 18 '12 at 16:50

Rob Paller

7,506
23
23

Rob, please could you be more specific about MERGE INTO operation? I have searched both internet and Teradata documentation and found nothing about it. Unfortunately i cannot use UDF library due to environment preconditions. – JohnyCash Apr 18 '12 at 18:02
I am working on 13.10. Please could you update your answer with example of MERGE INTO statement? – JohnyCash Apr 19 '12 at 07:52
Unfortunately, ERROR TABLEs do not support conversion errors logging. – nonsleepr May 05 '12 at 07:48

score 0 · Answer 2 · answered Apr 18 '12 at 23:26

0

You could write out the data from the old table using FastExport and then load it into the new table using FastLoad. Any records which parse to decimal(4, 0) will be loaded to the new table while the rest will be written out to error tables. Make sure to set a sufficiently high ERRLIMIT to ensure that the job doesn't die after a handful of errors.

answered Apr 18 '12 at 23:26

lins314159

2,440
1
16
19

Thanks for tip. Unfortunately I am doing this in certain part of DWH loading process where I cannot use FastExport/FastLoad and I am limited to plain Teradata SQL. – JohnyCash Apr 19 '12 at 10:48

JohnyCash · Accepted Answer · 2018-04-20T20:24:08.797

Finally with help of my colleagues I came to feasible solution. It has some limitations (sign is not considered, decimal part is not considered), but for IDs it works just fine.

Trim spaces from both beginning and end of string
Trim leading zeros from string
Test for maximum allowed length
Pad string with zeros to four chars (add four zeros to beginning of string and get last four characters from string)
Test each position in string on set of allowed chars

So the records from SRC_TABLE which cannot be converted to DECIMAL(4) can be obtained by select:

select 
  Some_Id
from
  SRC_TABLE
where
  characters(trim(leading '0' from trim(both ' ' from Some_Id))) > 4
  or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 1 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9')  
  or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 2 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
  or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 3 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
  or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 4 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9');

EDIT: More convenient is the way suggested by dnoeth in his answer to Convert char to int TeraData Sql , which also works in TD 13.10:

-- TO_NUMBER returns NULL when failing

CAST(TO_NUMBER(UTENTE_CD) AS INTEGER)

-- check if there are only digits
CASE WHEN UTENTE_CD  = ''                     -- all spaces
       THEN NULL
     WHEN LTRIM(UTENTE_CD, '0123456789') = '' -- only digits
       THEN CAST(UTENTE_CD AS INTEGER)
     ELSE NULL
END

Well done on coming up with a solution. Be aware though that while it works in your particular case, it is not equivalent to a proper numeric parser, since negative numbers and those which have a decimal point would be deemed invalid using this method. — lins314159, Apr 20 '12 at 00:07

Safe casting VARCHAR to DECIMAL in Teradata

3 Answers3