CODEWITHSUNDEEP
sqloracle-databasesnowflake-cloud-data-platform
Sudarshan
Sudarshan

Reputation: 19

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

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

Upvotes: 1

Views: 18116

Answers (4)

Dale C
Dale C

Reputation: 607

Another approach may be to use lateral joins - https://docs.snowflake.com/en/sql-reference/constructs/join-lateral.html

One important thing about lateral joins is it will only return matched records.

Upvotes: 0

Haseeb
Haseeb

Reputation: 196

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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;

Upvotes: 3

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2850

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 ...

Upvotes: 3

Related Questions