SSC-EWI-0054

Unsupported outer join subquery

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

Low

Description

This error happens when a correlated subquery is found within an OR logical expression of an OUTER JOIN (Left, Right or Full). In those cases they could produce inconsistent results or cause the following error:

SQL compilation error: Unsupported subquery type cannot be evaluated.

These limitations with subqueries are briefly mentioned in Snowflake documentation and some information about them can also be found in Snowflake forums.

Example code

Input Code (Teradata):

IN -> Teradata_01.sql

SELECT a.Column1, b.Column2
FROM
    TableA a
    LEFT JOIN TableB b ON (a.Column1 = b.Column1)
    AND (
        a.Column2 = b.Column2
        OR EXISTS(
            SELECT * FROM Table3 c
            WHERE c.Column1 = a.Column1
        )
    );

Output Code:

OUT -> Teradata_01.sql

// SnowConvert Helpers Code section is omitted.
SELECT
    a.Column1,
    b.Column2
FROM
    TableA a
   LEFT JOIN
        TableB b ON (a.Column1 = b.Column1)
   AND (
       a.Column2 = b.Column2
       OR EXISTS
                !!!RESOLVE EWI!!! /*** SSC-EWI-0054 - CORRELATED SUBQUERIES WITHIN AN OR EXPRESSION OF AN OUTER JOIN COULD CAUSE COMPILATION ERRORS ***/!!!(
                    SELECT
                        * FROM
                        Table3 c
                               WHERE c.Column1 = a.Column1
       )
   );

Recommendations

• Verify the output code does not produce a compilation error.

• Verify the output code's functional equivalence.

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