SSC-EWI-TD0091

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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 [email protected]