I have a query which contains Case statement and the output of the CASE is used to compare another column in Hive.I am not able to run the same query.Here is the query.
SELECT
AL1.RECORD_ID,
AL1.CARRIER_CODE,
AL1.ORIG_AP_CTY_CDE,
AL1.ORIG_STATE_CODE,
AL1.ORIG_COUNTRY_CODE,
AL1.ORIG_GEOGRAPHIC_ZONE_NUMBER,
CASE
WHEN AL1.ORIG_GEOGRAPHIC_ZONE_NUMBER > 99 THEN CONCAT(CAST(AL1.ORIG_GEOGRAPHIC_ZONE_NUMBER/10 AS INT), '0')
WHEN AL1.ORIG_GEOGRAPHIC_ZONE_NUMBER IN ('1','2','4','6','7','8','9') THEN '000'
ELSE AL1.ORIG_GEOGRAPHIC_ZONE_NUMBER
END AS ALL_ORIG_GEOGRAPHIC_ZONE,
AL1.ORIG_WORLD_AREA_NUMBER,
AL1.DEST_AP_CTY_CDE,
AL1.DEST_STATE_CODE,
AL1.DEST_COUNTRY_CODE,
AL1.DEST_GEOGRAPHIC_ZONE_NUMBER,
CASE
WHEN AL1.DEST_GEOGRAPHIC_ZONE_NUMBER > 99 THEN CONCAT(CAST(AL1.DEST_GEOGRAPHIC_ZONE_NUMBER/10 AS INT), '0')
WHEN AL1.DEST_GEOGRAPHIC_ZONE_NUMBER IN ('1','2','4','6','7','8','9') THEN '000'
ELSE AL1.DEST_GEOGRAPHIC_ZONE_NUMBER
END AS ALL_DEST_GEOGRAPHIC_ZONE,
AL1.DEST_WORLD_AREA_NUMBER,
AL1.FRBS_CDE,
AL1.OW_RT_CDE,
AL1.PUB_RULE_TRF_NUM,
AL1.FARE_RULE_NUM,
AL1.PUB_RTG_NUM,
AL1.PUB_FTNTE_ID_CDE,
AL1.FARE_TYPE_CODE,
AL1.SEASON_TYPE_CODE,
AL1.DAY_OF_WEEK_TYPE_CODE,
AL2.CATEGORY_CONTROL_ID,
AL2.CATEGORY_NUMBER AS GROUP_CATEGORY_NUMBER,
AL2.SEQUENCE_NUMBER,
AL2.LOCATION1_TYPE_CODE,
AL2.LOCATION1_CODE,
AL2.LOCATION2_TYPE_CODE,
AL2.LOCATION2_CODE,
AL2.FARE_CLASS_CODE,
AL2.GENERAL_RULE_TARIFF_NUMBER,
AL2.GENERAL_RULE_NUMBER,
AL2.GENERAL_RULE_IND,
'F' AS REC_IND
FROM TMP_TD_CNSTR_WINNING_FARES AL1
INNER JOIN TMP_TD_CNSTRPOST_CAT_CONTROL_ONLY_LIMITED_F AL2
ON (
AL1.CARRIER_CODE=AL2.CARRIER_CODE
AND AL1.PUB_RULE_TRF_NUM=AL2.TARIFF_NUMBER
AND AL1.FARE_RULE_NUM = AL2.RULE_FOOTNOTE_CODE
AND AL1.PUB_RTG_NUM = AL2.ROUTING_NUMBER
AND AL1.SEASON_TYPE_CODE = AL2.SEASON_TYPE_CODE
AND AL1.PUB_FTNTE_ID_CDE = AL2.FOOTNOTE_CODE
AND AL1.OW_RT_CDE = AL2.OW_RT_IND
AND AL1.FARE_TYPE_CODE = AL2.FARE_TYPE_CODE
AND AL1.DAY_OF_WEEK_TYPE_CODE = AL2. DAY_OF_WEEK_TYPE_CODE
)
INNER JOIN TMP_TD_CNSTRPOST_FRBS_MATCH_F AL4
ON ( AL1.CARRIER_CODE = AL4.CARRIER_CODE
AND AL1.PUB_RULE_TRF_NUM = AL4.PUB_RULE_TRF_NUM
AND AL1.FARE_RULE_NUM = AL4.RULE_NUM
AND AL1.FRBS_CDE = AL4.FRBS_CDE
AND AL2.FARE_CLASS_CODE = AL4.FARE_CLASS_CODE
)
WHERE
( (
(COALESCE(LOCATION1_TYPE_CODE, '') = '') OR
(LOCATION1_TYPE_CODE = 'C' AND LOCATION1_CODE = ORIG_AP_CTY_CDE) OR
(LOCATION1_TYPE_CODE = 'S' AND LOCATION1_CODE = ORIG_STATE_CODE) OR
(LOCATION1_TYPE_CODE = 'N' AND LOCATION1_CODE = ORIG_COUNTRY_CODE) OR
(LOCATION1_TYPE_CODE = 'Z' AND LOCATION1_CODE = ORIG_GEOGRAPHIC_ZONE_NUMBER) OR
(LOCATION1_TYPE_CODE = 'Z' AND LOCATION1_CODE = ALL_ORIG_GEOGRAPHIC_ZONE) OR
(LOCATION1_TYPE_CODE = 'A' AND LOCATION1_CODE = ORIG_WORLD_AREA_NUMBER)
)
AND
(
(COALESCE(LOCATION2_TYPE_CODE, '') = '') OR
(LOCATION2_TYPE_CODE = 'C' AND LOCATION2_CODE = DEST_AP_CTY_CDE) OR
(LOCATION2_TYPE_CODE = 'S' AND LOCATION2_CODE = DEST_STATE_CODE) OR
(LOCATION2_TYPE_CODE = 'N' AND LOCATION2_CODE = DEST_COUNTRY_CODE) OR
(LOCATION2_TYPE_CODE = 'Z' AND LOCATION2_CODE = DEST_GEOGRAPHIC_ZONE_NUMBER) OR
(LOCATION2_TYPE_CODE = 'Z' AND LOCATION2_CODE = ALL_DEST_GEOGRAPHIC_ZONE) OR
(LOCATION2_TYPE_CODE = 'A' AND LOCATION2_CODE = DEST_WORLD_AREA_NUMBER)
)
)
OR
((
(COALESCE(LOCATION1_TYPE_CODE, '') = '') OR
(LOCATION1_TYPE_CODE = 'C' AND LOCATION1_CODE = DEST_AP_CTY_CDE) OR
(LOCATION1_TYPE_CODE = 'S' AND LOCATION1_CODE = DEST_STATE_CODE) OR
(LOCATION1_TYPE_CODE = 'N' AND LOCATION1_CODE = DEST_COUNTRY_CODE) OR
(LOCATION1_TYPE_CODE = 'Z' AND LOCATION1_CODE = DEST_GEOGRAPHIC_ZONE_NUMBER) OR
(LOCATION1_TYPE_CODE = 'Z' AND LOCATION1_CODE = ALL_DEST_GEOGRAPHIC_ZONE) OR
(LOCATION1_TYPE_CODE = 'A' AND LOCATION1_CODE = DEST_WORLD_AREA_NUMBER)
)
AND
(
(COALESCE(LOCATION2_TYPE_CODE, '') = '') OR
(LOCATION2_TYPE_CODE = 'C' AND LOCATION2_CODE = ORIG_AP_CTY_CDE) OR
(LOCATION2_TYPE_CODE = 'S' AND LOCATION2_CODE = ORIG_STATE_CODE) OR
(LOCATION2_TYPE_CODE = 'N' AND LOCATION2_CODE = ORIG_COUNTRY_CODE) OR
(LOCATION2_TYPE_CODE = 'Z' AND LOCATION2_CODE = ORIG_GEOGRAPHIC_ZONE_NUMBER) OR
(LOCATION2_TYPE_CODE = 'Z' AND LOCATION2_CODE = ALL_ORIG_GEOGRAPHIC_ZONE) OR
(LOCATION2_TYPE_CODE = 'A' AND LOCATION2_CODE = ORIG_WORLD_AREA_NUMBER)
)
)
;
The error I am encountering is
FAILED: SemanticException [Error 10004]: Line 72:59 Invalid table alias or column reference 'ALL_ORIG_GEOGRAPHIC_ZONE
Please help me resolving the issue.Thanks in Advance!!
