SSC-EWI-TD0091

Expression converted as cast with possible errors due to missing dependencies.

Some parts in the output code are omitted for clarity reasons

Severity

Medium

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:

IN -> Teradata_01.sql

CREATE TABLE TAB (
    COL1 VARCHAR(23)
)

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

Output Code

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE TAB (
    COL1 VARCHAR(23)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    TO_DATE(
    COL1, 'YYYY/MM/DD') AS COL1,
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0091 - EXPRESSION CONVERTED AS CAST BY DEFAULT. CONVERSION MIGHT PRESENT ERRORS DUE TO MISSING DEPENDENCIES FOR 'AMBIGUOUS_EXPR'. ***/!!!
    AMBIGUOUS_EXPR :: DATE
    FROM
    TAB;

Recommendations

• If you need more support, you can email us at [e-mail]