Getting Databricks error Error SparkUnsupportedOperationException: [INTERNAL_ERROR] Cannot generate code for expression: outer

Question

In Databricks sql while executing SQL with NOT EXISTS operator (using correlated subquery) its not working. Getting Databricks error Error SparkUnsupportedOperationException: [INTERNAL_ERROR] Cannot generate code for expression: outer.

Below is the sql query

SELECT in_cs.COMM_ID AS CUSTOMER_SERVICE_EPIC_ID, 
   Data.CUR_VALUE_DATETIME AS VALUE_INSTANT,
FROM hive_metastore.RAW_CLARITY.SMRTDTA_ELEM_DATA Data
INNER JOIN hive_metastore.RAW_CLARITY.SMRTDTA_ELEM_VALUE Value
ON Data.HLV_ID = Value.HLV_ID
INNER JOIN hive_metastore.RAW_CLARITY.CLARITY_CONCEPT SmartDataElement
ON Data.ELEMENT_ID = SmartDataElement.CONCEPT_ID
INNER JOIN hive_metastore.RAW_CLARITY.CUST_SERVICE in_cs
ON Data.RECORD_ID_NUMERIC = in_cs.COMM_ID AND NOT EXISTS
  ( SELECT 1 FROM hive_metastore.RAW_CLARITY.CUST_SERVICE AS cs
    LEFT JOIN  hive_metastore.RAW_CLARITY.CAL_REFERENCE_CRM AS crc
    ON cs.COMM_ID = crc.REF_CRM_ID
    LEFT JOIN hive_metastore.RAW_CLARITY.CAL_COMM_TRACKING AS cct
    ON crc.COMM_ID = cct.COMM_ID
    WHERE cct.COMM_ID IS NULL AND in_cs.COMM_ID = cs.COMM_ID)

score 0 · Answer 1 · edited Aug 07 '23 at 12:20

0

I had a similar error but resolved it by applying as statement WHERE NOT EXISTS instead of AND NOT EXISTS.

edited Aug 07 '23 at 12:20

Koedlt

4,286
8
15
33

answered Aug 03 '23 at 12:44

Whaze Boo

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 11 '23 at 05:13

Getting Databricks error Error SparkUnsupportedOperationException: [INTERNAL_ERROR] Cannot generate code for expression: outer

1 Answers1