CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jay
Jay

Reputation: 11

SQL compilation error: Unsupported subquery type cannot be evaluated in Snowflake

I am facing SQL compilation error: Unsupported subquery type cannot be evaluated in one of the Snowflake instance used for QA for following query:

Select 
 CASE WHEN (Select count(USERSOURCE) From QA_DW.PRDODB.User Where 
 USERSOURCE=3) = 0  OR RECID not in (Select UD_SRC_KEY From QA_DW.PRDODB.User 
 Where 
 USERSOURCE=3)  
      THEN CreatedDateTime 
 ELSE CONVERT_TIMEZONE('US/Pacific','UTC',cast(CURRENT_TIMESTAMP() as 
      TIMESTAMP_NTZ)) 
 END  as FROM_DTIMEUTC
 FROm QA_STAGING.HISTODB.STG_User_TBL

The same query runs in DEV instance.

Upvotes: 0

Views: 916

Answers (1)

Ehsan Hemati
Ehsan Hemati

Reputation: 13

use this Query instead of the above query : 

SELECT 
CASE WHEN c.COUNT_USERSOURCE = 0 OR RECID NOT IN (SELECT UD_SRC_KEY FROM 
QA_DW.PRDODB.USER WHERE USERSOURCE=3)
THEN CreatedDateTime 
ELSE CONVERT_TIMEZONE('US/Pacific','UTC',CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)) 
END AS FROM_DTIMEUTC 
FROM QA_STAGING.HISTODB.STG_User_TBL
OUTER APPLY (
 SELECT 
 COUNT(USERSOURCE)  AS COUNT_USERSOURCE
 FROM QA_DW.PRDODB.USER WHERE USERSOURCE=3
 ) AS C

Upvotes: 1

Related Questions