谢谢jerrick。它为我使用派生表工作。这是更新的查询。

SELECT
                *
              FROM (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
                )) AS WF
              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 = WF.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 = WF.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 = WF.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 = WF.ALL_ORIG_GEOGRAPHIC_ZONE)
              OR (LOCATION2_TYPE_CODE = 'A'
              AND LOCATION2_CODE = ORIG_WORLD_AREA_NUMBER)
              )
              )
            ;

where子句在select子句之前求值，因此不能在where子句中使用所有目的地地理区域。可以在派生表或having子句中尝试。
相关问题