title: Getting error "SQL compilation error: Unsupported subquery type cannot be evaluated"
body: <p>Am new to Snowflake programming though I had much experience in Oracle DB.</p>
  
  <p>When am running the below query in Snowflake am getting the error as </p>
  
  <blockquote>
    <p>SQL compilation error: Unsupported subquery type cannot be evaluated</p>
  </blockquote>
  
  <pre class="lang-sql prettyprint-override"><code>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 &lt;= 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);
  </code></pre>
  
  <p>Am I missing something here??</p>
  
  <p>Can someone plz help me here.</p>
  
  <p>Thanks in Advance,
  Sudarshan</p>