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
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.

MSCEWI2082

MSCEWI2084

Last modified 3mo ago