Snowflake报错Unsupported subquery type cannot be evaluated，求SQL查询协助

报错原因分析 #

报错Unsupported subquery type cannot be evaluated通常是因为当前SQL引擎不支持你使用的标量子查询写法，另外你的查询还存在严重问题：所有表用逗号连接但未指定任何关联条件，这会产生巨大的笛卡尔积，不仅性能极差，也可能加剧子查询的兼容性问题。

修复后的查询语句 #

将标量子查询替换为LEFT JOIN，同时补充表之间的关联条件（请根据实际业务逻辑调整ON后的关联字段）：

select 
    food.ORGANIZATION_CODE as from_organization_code
    , hlf.ADDRESS_LINE_1 
    , hlt.TOWN_OR_CITY to_town_or_city
    , hlt.POSTAL_CODE to_postal_code
    , hlt.COUNTRY to_country
    , fl.MEANING as ship_method
    , mism.ATTRIBUTE4
    , hlf.LOCATION_CODE
    , hlt.LOCATION_CODE
from EDW_SERVICE_ETL_DB.SS.CSF_MTL_INTERORG_SHIP_METHODS mism
-- 替换标量子查询为LEFT JOIN
left join EDW_SERVICE_ETL_DB.SS.FND_LOOKUP_VALUES fl 
    on fl.LOOKUP_TYPE = 'SHIP_METHOD' and fl.LOOKUP_CODE = mism.SHIP_METHOD
-- 以下为各表关联条件，请根据实际业务逻辑补充完整
join EDW_SERVICE_ETL_DB.SS.CSF_HR_LOCATIONS_ALL hlf
    on -- 补充mism与hlf的关联条件，例如 mism.FROM_LOCATION_ID = hlf.LOCATION_ID
join EDW_SERVICE_ETL_DB.SS.CSF_HR_LOCATIONS_ALL hlt
    on -- 补充mism与hlt的关联条件，例如 mism.TO_LOCATION_ID = hlt.LOCATION_ID
join EDW_SERVICE_ETL_DB.SS.ORG_ORGANIZATION_DEFINITIONS food
    on -- 补充mism与food的关联条件，例如 mism.FROM_ORGANIZATION_ID = food.ORGANIZATION_ID
join EDW_SERVICE_ETL_DB.SS.CSF_HR_ALL_ORGANIZATION_UNITS fee
    on -- 补充food与fee的关联条件，例如 food.ORGANIZATION_ID = fee.ORGANIZATION_ID
join EDW_SERVICE_ETL_DB.SS.ORG_ORGANIZATION_DEFINITIONS tood
    on -- 补充mism与tood的关联条件，例如 mism.TO_ORGANIZATION_ID = tood.ORGANIZATION_ID
join EDW_SERVICE_ETL_DB.SS.CSF_HR_ALL_ORGANIZATION_UNITS thou
    on -- 补充tood与thou的关联条件，例如 tood.ORGANIZATION_ID = thou.ORGANIZATION_ID
order by 
    hlf.LOCATION_CODE,
    hlt.LOCATION_CODE

关键修改说明 #

• 用LEFT JOIN替代原标量子查询，避免引擎不支持的子查询类型，同时逻辑上保持一致（如果SHIP_METHOD无匹配值，ship_method会返回NULL）。
• 必须为所有表连接添加明确的ON条件，否则会生成笛卡尔积，导致结果错误且性能极低。

内容的提问来源于stack exchange，提问作者Devaraj Mani Maran