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?