MSCEWI2046

Temporary Table might have duplicated rows.

This issue has been deprecated since SnowConvert for Teradata 2.0.21

Teradata's Table Kind Clause default behavior will depend on the Session Mode. If the session creating the table is set as ANSI, duplicate rows are allowed (MULTISET), being functionally equivalent to Snowflake's behavior. However, if the session creating the table is set as Teradata, duplicate rows are not allowed (SET), which is not an option supported in Snowflake.

For more information check Different SQL Table Definitions in Teradata & Snowflake and MSCEWI2015

Severity

Low

Description

Temporary Table might have duplicated rows.

Example Code

Input Code:

CREATE VOLATILE TABLE SOMETABLE, LOG AS 
(SELECT BUS_PLAN_MEAS_DTL_ROW_ID
, BUS_PLAN_TYP_CD
, MEAS_PLAN_TYP_CD
, MEAS_SUB_PLAN_TYP_CD
, WSLR_PARTY_ID FROM T1);


CREATE SET VOLATILE TABLE SOMETABLE, LOG AS 
(SELECT BUS_PLAN_MEAS_DTL_ROW_ID
, BUS_PLAN_TYP_CD
, MEAS_PLAN_TYP_CD
, MEAS_SUB_PLAN_TYP_CD
, WSLR_PARTY_ID FROM T2);

Output Code:

/*** MSC-WARNING - MSCEWI2046 - Temporary Table might have duplicated rows ***/
CREATE TEMPORARY TABLE DATAWAREHOUSE.PUBLIC.SOMETABLE AS
(SELECT
BUS_PLAN_MEAS_DTL_ROW_ID,
BUS_PLAN_TYP_CD,
MEAS_PLAN_TYP_CD,
MEAS_SUB_PLAN_TYP_CD,
WSLR_PARTY_ID
FROM DATAWAREHOUSE.PUBLIC.T1) ;

/*** MSC-WARNING - MSCEWI2015 - SET TABLE FUNCTIONALITY NOT SUPPORTED ***/
/*** MSC-WARNING - MSCEWI2046 - Temporary Table might have duplicated rows ***/
CREATE TEMPORARY TABLE DATAWAREHOUSE.PUBLIC.SOMETABLE AS
(SELECT
BUS_PLAN_MEAS_DTL_ROW_ID,
BUS_PLAN_TYP_CD,
MEAS_PLAN_TYP_CD,
MEAS_SUB_PLAN_TYP_CD,
WSLR_PARTY_ID
FROM DATAWAREHOUSE.PUBLIC.T2) ;

Recommendations