MSCEWI2083

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

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

Last updated