mysql - Hive 查询问题 - 无效的表别名或列引用

Question

我有一个包含 Case 语句的查询，CASE 的输出用于比较 Hive 中的另一列。我无法运行相同的查询。这是查询。

    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)
             )
     )
    ;

我遇到的错误是

失败:SemanticException [错误 10004]:第 72:59 行无效的表别名或列引用 'ALL_ORIG_GEOGRAPHIC_ZONE

请帮我解决这个问题。提前致谢!

Answer 1

where 子句在 select 子句之前计算，因此您不能在 where 子句中使用 ALL_DEST_GEOGRAPHIC_ZONE。您可以在派生表或 having 子句中尝试。

relevant question