MSCEWI4030

Warning while OUTER JOIN equivalence is checked.

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

Severity

Low

Description

The current equivalency used to represent the OUTER APPLY in Snowflake is the INNER JOIN LATERAL and the equivalence for CROSS APPLY is LEFT OUTER JOIN, but this target statement must be checked and confirm if this transformation rule is accurate.

Code Example

Input Code:

SELECT TOPS.* from Customers as C
OUTER APPLY (SELECT TOP 1 O.code 
    FROM Orders AS O
    WHERE O.customer_id = C.customer_id
    ORDER BY O.code) AS TOPS;

Output Code:

SELECT TOPS.* from Customers as C 
INNER JOIN LATERAL (SELECT TOP 1 O.code
    FROM Orders AS O
    WHERE O.customer_id = C.customer_id
    ORDER BY O.code) AS TOPS /*** MSC-WARNING - MSCEWI4030 - Equivalence from OUTER APPLY to LEFT OUTER JOIN must be checked. ***/;

Recommendations

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