I have the following query in Spark-
<code>SELECT bu.tenant_id,bu.service_location_id,bu.account_id,bu.commodity_type,bu.commodity_usage,bu.commodity_units,bu.charges,bu.billed_usage_start,bu.billed_usage_end,
CASE
WHEN al.industry_code_type IS NOT NULL AND al.industry_code IS NOT NULL AND al.industry_code_type= 'sic'
THEN (SELECT sics_name FROM dev_silver.trend_calculator_poc.mapping_business_type_codes_results
where sics_code=CAST(al.industry_code AS BIGINT) limit 1)
END
as business_type, bu.created AS created_at, bu.updated AS updated_at FROM dev_silver.trend_calculator_poc.billed_usage_temp bu,dev_silver.trend_calculator_poc.account_location_temp al
where al.tenant_id=bu.tenant_id AND al.service_location_id=bu.service_location_id and al.account_id=bu.account_id;
</code>
<code>SELECT bu.tenant_id,bu.service_location_id,bu.account_id,bu.commodity_type,bu.commodity_usage,bu.commodity_units,bu.charges,bu.billed_usage_start,bu.billed_usage_end,
CASE
WHEN al.industry_code_type IS NOT NULL AND al.industry_code IS NOT NULL AND al.industry_code_type= 'sic'
THEN (SELECT sics_name FROM dev_silver.trend_calculator_poc.mapping_business_type_codes_results
where sics_code=CAST(al.industry_code AS BIGINT) limit 1)
END
as business_type, bu.created AS created_at, bu.updated AS updated_at FROM dev_silver.trend_calculator_poc.billed_usage_temp bu,dev_silver.trend_calculator_poc.account_location_temp al
where al.tenant_id=bu.tenant_id AND al.service_location_id=bu.service_location_id and al.account_id=bu.account_id;
</code>
SELECT bu.tenant_id,bu.service_location_id,bu.account_id,bu.commodity_type,bu.commodity_usage,bu.commodity_units,bu.charges,bu.billed_usage_start,bu.billed_usage_end,
CASE
WHEN al.industry_code_type IS NOT NULL AND al.industry_code IS NOT NULL AND al.industry_code_type= 'sic'
THEN (SELECT sics_name FROM dev_silver.trend_calculator_poc.mapping_business_type_codes_results
where sics_code=CAST(al.industry_code AS BIGINT) limit 1)
END
as business_type, bu.created AS created_at, bu.updated AS updated_at FROM dev_silver.trend_calculator_poc.billed_usage_temp bu,dev_silver.trend_calculator_poc.account_location_temp al
where al.tenant_id=bu.tenant_id AND al.service_location_id=bu.service_location_id and al.account_id=bu.account_id;
The query is resulting in an error Key not found: industry_code#15252. The error disappears if I remove the where condition in the sub-query in the THEN clause. Somehow, it is related to access of outer table alias al inside subquery but I am not able figure it out. The data is also present for the join so it is definitely not because of unavailability of any result by subquery. Am I accessing the outer table alias in subquery correctly?
Please suggest how this can be fixed