CODEWITHSUNDEEP
snowflake-cloud-data-platform
Nidhi
Nidhi

Reputation: 21

getting in snowflake SQL compilation error

getting in snowflake SQL compilation error: Unsupported subquery type cannot be evaluated. while using lookup table in query

like--

select
  field1,
  (select 
     L.FIELD_DESC 
   from "STG"."LKP_CD_DESC" L 
   where S.field2 = L.FIELD_CD and FIELD_NAME='ABC'
   ) as  field_DESC
from "STG"."table1"  S;

Upvotes: 0

Views: 322

Answers (3)

Nidhi
Nidhi

Reputation: 21

SELECT S.field1, L.FIELD_DESC AS field_DESC FROM "STG"."table1" S LEFT JOIN "STG"."LKP_CD_DESC" L ON S.field2 = L.FIELD_CD AND L.FIELD_NAME = 'ABC'; In this query, we use a LEFT JOIN to join the "table1" table with the "LKP_CD_DESC" lookup table on the condition S.field2 = L.FIELD_CD and L.FIELD_NAME = 'ABC'. The result will include all records from "table1" and match the corresponding "FIELD_DESC" from the lookup table, if available.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

This particular query could be rewritten as LEFT JOIN:

SELECT S.field1,
       L.FIELD_DESC
FROM "STG"."table1" AS S
LEFT JOIN "STG"."LKP_CD_DESC" AS L
  ON S.field2=L.FIELD_CD 
 AND L.FIELD_NAME='ABC';

The only difference is when it exists more than one FIELD_DESC per join conditions:

  • original subquery would return error as scalar subqueries cannot return more than one row
  • LEFT JOIN "duplicates" row from S table

Upvotes: 1

sav
sav

Reputation: 197

Snowflake has some limitations with subqueries.

https://docs.snowflake.com/en/user-guide/querying-subqueries.html#types-supported-by-snowflake

Better to rewrite the query using joins

Upvotes: 0

Related Questions