Getting error "SQL compilation error: Unsupported subquery type cannot be evaluated"

Question

Am new to Snowflake programming though I had much experience in Oracle DB.

When am running the below query in Snowflake am getting the error as

SQL compilation error: Unsupported subquery type cannot be evaluated

SELECT organization_id,
       inventory_item_id,
       revision,
       effectivity_date,
       revision_label,
       revision_id
  FROM cg1_mtl_item_revisions_b mir
 WHERE     effectivity_date IN
              (SELECT FIRST_VALUE (ir2.effectivity_date)
                         OVER (ORDER BY ir2.effectivity_date DESC)
                         effectivity_date
                 FROM cg1_mtl_item_revisions_b ir2
                WHERE     ir2.inventory_item_id = mir.inventory_item_id
                      AND ir2.organization_id = mir.organization_id
                      AND ir2.effectivity_date <= CURRENT_DATE
                      AND ir2.implementation_date IS NOT NULL)
       AND mir.revision IN
              (SELECT FIRST_VALUE (ir3.revision)
                         OVER (ORDER BY ir3.revision DESC)
                         revision
                 FROM cg1_mtl_item_revisions_b ir3
                WHERE     ir3.inventory_item_id = mir.inventory_item_id
                      AND ir3.organization_id = mir.organization_id
                      AND ir3.implementation_date IS NOT NULL
                      AND ir3.effectivity_date = mir.effectivity_date);

Am I missing something here??

Can someone plz help me here.

Thanks in Advance, Sudarshan

Answer 1

The Snowflake database doesn't support correlated subqueries as extensively as Oracle does.
You have to find a way to rewrite, eg. using

WITH <common table expressions ...>
SELECT ...
JOIN ...

Answer 2

You seem to want the latest revision from the latest effective date. Window functions are probably a better approach in any database:

SELECT mir.*  -- whatever columns you want
FROM (SELECT mir.*,
             ROW_NUMBER() OVER (PARTITION BY mir.inventory_item_id, mir.organization_id
                                ORDER BY mir.effectivity_date DESC, mir.revision DESC) as seqnum
      FROM cg1_mtl_item_revisions_b mir
     ) mir
WHERE seqnum = 1;

Answer 3

I have figured out that the third condition of the join in the last sub-query is throwing this error. If we comment out the following line then the code returns data:

{ AND ir3.effectivity_date = mir.effectivity_date }

So it seems three join conditions in a sub-query are not getting supported. We need to work out on finding an alternate piece of code to satisfy the above condition so that we get the correct result set.