Snowflake: OUTER APPLY / LATERAL

Viewed 460

Snowflake apparently doesn't support OUTER APPLY. However it has LATERAL, which from what I can see, works like OUTER APPLY.

But when I try to use LATER in this way, I get a compilation error that I'm not sure how to resolve.

Here is the query:

SELECT
   *
FROM
    temp_report r
        CROSS JOIN LATERAL
            (
                SELECT
                    ROW_NUMBER() OVER (PARTITION BY rr.state_code ORDER BY rr.priority) as rule_priority,
                    *
                FROM
                    rule_rules rr
                        LEFT JOIN rule_classes rc ON rr.state_code = rc.state_code AND rr.class_group = rc.class_group
                WHERE
                    r.state_code = rr.state_code AND
                    (r.class = rc.class OR rc.class IS NULL) AND
                    ((r.years = 0 AND first_term = true) OR (r.years > 0 AND first_term = false)) AND
                    (r.years-1 % rule_interval = 0 OR rule_interval IS NULL)
            ) rr
WHERE
    rr.priority IS NULL OR rr.rule_priority = 1

The error I get is : Unsupported subquery type cannot be evaluated.

Suggestions?

0 Answers
Related