SSC-EWI-TD0083

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

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:

IN -> Teradata_01.sql

SELECT 
   NORMALIZE emp_id,
   duration,
   dept_id, 
   balance, 
   (
     ROW_NUMBER() OVER (
       PARTITION BY emp_id 
       ORDER BY 
         dept_id RESET WHEN balance <= SUM(balance) OVER (
           PARTITION BY emp_id 
           ORDER BY dept_id
           ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
         )
     ) -1
   ) AS balance_increase 
FROM project
EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND
ORDER BY 1, 2;

Output Code

OUT -> Teradata_01.sql

// SnowConvert Helpers Code section is omitted.
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0083 - NOT ABLE TO TRANSFORM TWO OR MORE COMPLEX SELECT CLAUSES AT A TIME ***/!!!
NORMALIZE emp_id,
   duration,
   dept_id,
   balance,
   (
     ROW_NUMBER() OVER (
   PARTITION BY
      emp_id, new_dynamic_part
   ORDER BY
         dept_id
     ) -1
   ) AS balance_increase
FROM
   (
      SELECT
         emp_id,
         duration,
         dept_id,
         balance,
         previous_value,
         SUM(dynamic_part) OVER (
                 PARTITION BY emp_id
                 ORDER BY dept_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ) AS new_dynamic_part
      FROM
         (
            SELECT
               emp_id,
               duration,
               dept_id,
               balance,
               SUM(balance) OVER (
                       PARTITION BY emp_id
                       ORDER BY dept_id
                       ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
                     ) AS previous_value,
               (CASE
                  WHEN balance <= previous_value
                     THEN 1
                  ELSE 0
               END) AS dynamic_part
            FROM
               project
         )
   )
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0083 - NOT ABLE TO TRANSFORM TWO OR MORE COMPLEX SELECT CLAUSES AT A TIME ***/!!!
EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND
ORDER BY 1, 2;

Recommendations

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