Teradata - Column name on which error occurred

3
votes

We don't have access to our Teradata PROD and we develop scripts and test in SIT, UAT. When promoted to PROD, occasionally the following errors occur:

Invalid Date/Timestamp
Numeric overflow occurred
Untranslatable character
....

Why doesn't Teradata show the exact column name on which the error occurred?

We need to go through the script where around 20 columns are being casted from varchar to date/timestamp and around 10 columns are prone to Numeric overflow occurred. We need to individually go through each column expecting this might be the culprit one. Will be more relieved when the error does show up the column name.

I am sure that as it was not implemented till now, assume that this should have been more complex due to run time errors. However, the ET_, UV_ error tablenames does capture some of these errors, I guess (may be not all).

Can you please explain when it was possible on ET_, UV_ tables, why can't be it implemented for a normal SQL query to show on which column the error occurred?

1
teradatacolumnname
How are you executing these queries? – Andrew

1 Answers

1
votes

These runtime errors are associated with an operation on some value, not necessarily with a particular column -- it could also be a result of an expression.

I imagine associating all the fallible expressions in a query with the corresponding parts of the original SQL would incur certain overhead. It would definitely require non-trivial amount of development work. You might want to ask your Teradata representative about this.

The ET/UV tables are maintained by TPT, which handles external data and is more likely to encounter unexpected values.

If this is a common situation, perhaps you need to cleanse your data. There's usually a way to find the rows that cause the listed errors using built-in SQL functions or UDFs, for example:

(There doesn't appear to be a common way to check if a CAST will succeed.)