How to check if decimal is valid value in teradata?

@muriel.schmidt 

To check if a decimal value is valid in Teradata, you can use the following approach:

1. Check if the decimal value falls within the allowed range for the DECIMAL data type. The DECIMAL data type in Teradata can store decimal numbers with a precision ranging from 1 to 18 digits and a scale ranging from -128 to 127. If the decimal value you are trying to check falls outside of this range, it is considered invalid.
2. You can also check if the decimal value is NULL or contains any characters other than digits, a decimal point, or a minus sign. This can be done using a combination of functions such as ISNUMERIC and CASE statements in Teradata SQL.

Here is an example of how you can check if a decimal value is valid in Teradata:

1
2
3
4
5
6
7
8

SELECT 
    CASE 
        WHEN MyDecimalColumn IS NULL THEN 'Invalid'
        WHEN MyDecimalColumn NOT BETWEEN -999999999999999.99999999999999 AND 999999999999999.99999999999999 THEN 'Invalid'
        WHEN ISNUMERIC(CAST(MyDecimalColumn AS VARCHAR)) = 0 THEN 'Invalid'
        ELSE 'Valid'
    END AS DecimalValidity
FROM MyTable;

In this example, replace MyDecimalColumn with the name of the column containing the decimal value you want to check, and MyTable with the name of the table containing the column. The query will return 'Valid' if the decimal value is within the allowed range and contains only valid characters, and 'Invalid' otherwise.