Snowflake error: Unsupported subquery type cannot be evaluated

Question

Need help with the below query. I'm getting error when executing.

Unsupported subquery type cannot be evaluated.

    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
    , 
    (
    select fl.MEANING
    from EDW_SERVICE_ETL_DB.SS.FND_LOOKUP_VALUES as fl
    where fl.LOOKUP_TYPE = 'SHIP_METHOD'
    and fl.LOOKUP_CODE = mism.SHIP_METHOD
    ) ship_method
    , mism.ATTRIBUTE4
,hlf.LOCATION_CODE
, hlt.LOCATION_CODE
    from EDW_SERVICE_ETL_DB.SS.CSF_MTL_INTERORG_SHIP_METHODS mism
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_LOCATIONS_ALL hlf
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_LOCATIONS_ALL hlt
    , EDW_SERVICE_ETL_DB.SS.ORG_ORGANIZATION_DEFINITIONS food
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_ALL_ORGANIZATION_UNITS fee
    , EDW_SERVICE_ETL_DB.SS.ORG_ORGANIZATION_DEFINITIONS tood
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_ALL_ORGANIZATION_UNITS thou
    
    order by 
    hlf.LOCATION_CODE,
    hlt.LOCATION_CODE

Thanks in advance.

Answer 1

Try moving the subquery to the main body of the query

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 ship_method
    , mism.ATTRIBUTE4
    , hlf.LOCATION_CODE
    , hlt.LOCATION_CODE
from EDW_SERVICE_ETL_DB.SS.CSF_MTL_INTERORG_SHIP_METHODS mism
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_LOCATIONS_ALL hlf
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_LOCATIONS_ALL hlt
    , EDW_SERVICE_ETL_DB.SS.ORG_ORGANIZATION_DEFINITIONS food
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_ALL_ORGANIZATION_UNITS fee
    , EDW_SERVICE_ETL_DB.SS.ORG_ORGANIZATION_DEFINITIONS tood
    , EDW_SERVICE_ETL_DB.SS.CSF_HR_ALL_ORGANIZATION_UNITS thou
    , EDW_SERVICE_ETL_DB.SS.FND_LOOKUP_VALUES as fl
where fl.LOOKUP_TYPE = 'SHIP_METHOD'
  and fl.LOOKUP_CODE = mism.SHIP_METHOD
order by 
    hlf.LOCATION_CODE,
    hlt.LOCATION_CODE