MSCEWI2003

Collation not supported.

Severity

Medium

Description

Performing a TRIM LEADING, TRAILING, OR BOTH using a column with COLLATE as a parameter and a character different than whitespace as trim characters, is not supported in the Snowflake environment. The collation code is transformed into a stub.

Code Example

Input Code:

CREATE SET TABLE T_2003 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
-- NOT CASESPECIFIC is transformed to COLLATE 'en-ci'.
COL1 VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
COL2 VARCHAR(15) CHARACTER SET LATIN
);
REPLACE VIEW V_2003
AS
SELECT
TRIM(LEADING '0' FROM COL1) As COL1A -- COL1 has COLLATE, this TRIM is not supported.
FROM T_2003;

Output Code:

CREATE TABLE PUBLIC.T_2003
(
COL1 VARCHAR(15) COLLATE 'en-ci',
COL2 VARCHAR(15)
);
‚Äč
CREATE OR REPLACE VIEW PUBLIC.V_2003
AS
SELECT
/*** MSC-ERROR - MSCEWI2003 - COLLATION NOT SUPPORTED ***/
TRIM_STUB('-- COL1 has COLLATE, this TRIM is not supported.TRIM(LEADING \'0\' FROM COL1)') As COL1A,
/*** MSC-ERROR - MSCEWI2003 - COLLATION NOT SUPPORTED ***/
LTRIM_STUB('-- COL1 has COLLATE, this TRIM is not supported.LTRIM( COL1, \'0\')'),
/*** MSC-ERROR - MSCEWI2003 - COLLATION NOT SUPPORTED ***/
RTRIM_STUB('-- COL1 has COLLATE, this TRIM is not supported.RTRIM( COL1, \'0\')'),
/*** MSC-ERROR - MSCEWI2003 - COLLATION NOT SUPPORTED ***/
TRIM_STUB('-- COL1 has COLLATE, this TRIM is not supported.TRIM(TRAILING \'0\' FROM COL1)')
FROM PUBLIC.T_2003;

Recommendations

  • It is possible to remove the COLLATE 'en-ci' portion from the transformed code, and to uncomment the usage of the TRIM function, but that will mean that executing an ORDER BY over the column that had the COLLATE may not yield the same results as in the original code.

  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.