MSCEWI2091

Expression converted as cast with possible errors due to missing dependencies

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

In Teradata scripts, you can use the following syntax to CAST expressions:

<expression> ( <DataType> )

Unfortunately, this syntax generates ambiguity when trying to convert a CAST to DATE or TIME since these keywords also behave as the CURRENT_DATE and CURRENT_TIME functions respectively.

Thus, without context about the expression to be CAST, there is no sure way to differentiate when we are dealing with an actual case of CAST or a function that accepts DATE or TIME as parameters.

In other words, it is required to know whether <expression> is a column or a user-defined function (UDF). To achieve this, when converting the code, one must add the CREATE TABLE or CREATE FUNCTION from which <expression> is dependant on.

E.g. check the following SELECT statement. With no context about AMBIGUOUS_EXPR, we have no way to determine if we are dealing with a function call or CAST to DATE. However, we do know that COL1 (DATE) is indeed a CAST since COL1 is a column from the table TAB.

CREATE TABLE TAB (
    COL1 VARCHAR(23)
)

SELECT 
    COL1 (DATE),
    AMBIGUOUS_EXPR (DATE)
FROM TAB;

Example code

Input code

CREATE TABLE TAB (
    COL1 VARCHAR(23)
)

SELECT 
    COL1 (DATE),
    AMBIGUOUS_EXPR (DATE)
FROM TAB;

Output code

CREATE TABLE TAB (
    COL1 VARCHAR(23),
    COL2 TIME,
    COL3 DATE
);

SELECT
    TO_DATE(COL1, 'YYYY/MM/DD') AS COL1,
    --** MSC-WARNING - MSCEWI2091 - EXPRESSION CONVERTED AS CAST BY DEFAULT. CONVERSION MIGHT PRESENT ERRORS DUE TO MISSING DEPENDENCIES FOR 'COL4'. **
    COL4 :: DATE
FROM
    TAB;

Recommendations

• Add the necessary dependencies when converting the code.

• If you need more support, you can email us at [email protected]