MSCEWI2083

Not able to transform two or more complex Select clauses at a time

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

SnowConvert is not able to transform two or more complex SELECT clauses, as it is necessary to map them to a CTE or composite FROM clause, which causes the mapped code to not compile or enter into a logical cycle.

What do we consider a SELECT complex clause?

Those that required to be mapped to a CTE or composite FROM clause such as NORMALIZE, EXPAND ON, or RESET WHEN.

Example code

Input code

SELECT 
  NORMALIZE emp_id, 
  duration 
FROM 
  project EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND;

Output code

The transformation of the EXPAND ON clause is performed however the NORMALIZE clause is commented on due to the explanation above.

WITH ExpandOnCTE AS
(
   SELECT
      PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'EXPAND_ON_UDF' INSERTED. ***/ bg
   FROM
      PUBLIC.project,
      TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFFTTIME_PERIOD_UDF('ANCHOR_SECOND', duration) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DIFFTTIME_PERIOD_UDF' INSERTED. ***/) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'ROW_COUNT_UDF' INSERTED. ***/))
)
SELECT
-- ** MSC-ERROR - MSCEWI2083 - NOT ABLE TO TRANSFORM TWO OR MORE COMPLEX SELECT CLAUSES AT A TIME **
--       NORMALIZE
                 emp_id,
   duration
FROM
   PUBLIC.project,
   ExpandOnCTE
-- ** MSC-WARNING - MSCEWI2078 - THE EXPAND ON CLAUSE FUNCTIONALITY IS TRANSFORMED INTO A CTE BLOCK **
--EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND
                                                ;

Recommendations

• If you need more support, you can email us at [email protected]