MSCEWI2001

Recursive forward alias error.

Severity

Low

Description

This message is shown whenever SnowConvert detects recursion within aliased expressions, therefore being unable to execute the Forward Alias transformation required for the correct functionality of aliases within SnowFlake environment.

A recursive alias happens when an aliased expression contains another alias, and the second aliased expression contains the first alias. This may not be as trivial as the example shows, since the recursion can happen further down the line in a transitive way.

Code Example

Input Code:

SELECT
foo(c2) as c1,
foo(c1) as c2

Output Code:

foo(/*** MSC-WARNING- MSCEWI2001 - 'c2' HAS RECURSIVE REFERENCES. FORWARD ALIAS CONVERSION COULD NOT BE COMPLETED ***/
c2) as c1

Note: Recursive alias are not supported in Snowflake, however, some simple instances are.

Note that recursive alias is not supported in Snowflake, however, some simple instances are. Check examples below.

The following example code works in Snowflake after migration:

Input Code:

SELECT
COL1 AS COL2,
COL2 AS COL1,
COL3
FROM
TABLE_EXAMPLE

Output Code:

SELECT
/*** MSC-WARNING - MSCEWI2001 - 'COL1' HAS RECURSIVE REFERENCES. FORWARD ALIAS CONVERSION COULD NOT BE COMPLETED ***/
COL1 AS COL2,
COL2 AS COL1
FROM PUBLIC.TABLE_EXAMPLE;

This example code does not work:

Input Code:

SELECT
A + B as C,
COL2 + C AS A,
COL3 AS B
FROM
TABLE_EXAMPLE

Output Code:

SELECT
/*** MSC-WARNING - MSCEWI2001 - 'A' HAS RECURSIVE REFERENCES. FORWARD ALIAS CONVERSION COULD NOT BE COMPLETED ***/
/*** MSC-WARNING - MSCEWI2001 - 'A' HAS RECURSIVE REFERENCES. FORWARD ALIAS CONVERSION COULD NOT BE COMPLETED ***/
A +
COL3 as C,
COL2 + C AS A,
COL3 AS B
FROM PUBLIC.TABLE_EXAMPLE;

Recommendations

  • Review your code and make sure recursive forward aliases are not present. The EWI shows the name of the first instance of an alias that has recursive references, but that does not mean that is the only one that has them in your 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.