group by 与decode结合使用时的一个小教训

最重要的一点：如果在字段中使用DECODE，在GROUP BY后面对应地也要用DECODE，不能用字段别名。

如下面这样：

SELECT v.body_type body_type, btc.interpretation body_type_interpretation, v.veh_class||v.legal_id veh_class,
                  decode(vccl.legal_id, 2688781,vcc.interpretation, 'PUBLIC BUS(MTRC)') veh_class_interpretation,
                   NVL (SUM (1), 0) no_of_reg_veh,
                  NVL (SUM (CASE
                               WHEN 1=1--v.upto_date >= TRUNC (pkg_rpt_common.get_report_date_param) + 1 - 0.00001
                                  THEN 1
                               ELSE 0
                            END), 0) no_of_lic_veh
         FROM vehicle v,vehicle_class_code vcc,BODY_TYPE_CODE btc,veh_com_capping_limit vccl
         WHERE v.veh_class = '035'
               AND v.reg_mark IS NOT NULL
               AND v.body_type IS NOT NULL
               AND v.veh_class = vcc.veh_class
               AND v.body_type = btc.body_type_code
               AND v.veh_class = vccl.veh_class
               AND v.legal_id = vccl.legal_id
         GROUP BY v.body_type, btc.interpretation,v.veh_class,v.legal_id,veh_class_interpretation

就会报错，veh_class_interpretation被认为非法。

而将veh_class_interpretation替换为decode(vccl.legal_id, 2688781,vcc.interpretation, 'PUBLIC BUS(MTRC)') ，则运行正常。

如下所示：

SELECT v.body_type body_type, btc.interpretation body_type_interpretation, v.veh_class||v.legal_id veh_class,
                  decode(vccl.legal_id, 2688781,vcc.interpretation, 'PUBLIC BUS(MTRC)') veh_class_interpretation,
                   NVL (SUM (1), 0) no_of_reg_veh,
                  NVL (SUM (CASE
                               WHEN 1=1--v.upto_date >= TRUNC (pkg_rpt_common.get_report_date_param) + 1 - 0.00001
                                  THEN 1
                               ELSE 0
                            END), 0) no_of_lic_veh
         FROM vehicle v,vehicle_class_code vcc,BODY_TYPE_CODE btc,veh_com_capping_limit vccl
         WHERE v.veh_class = '035'
               AND v.reg_mark IS NOT NULL
               AND v.body_type IS NOT NULL
               AND v.veh_class = vcc.veh_class
               AND v.body_type = btc.body_type_code
               AND v.veh_class = vccl.veh_class
               AND v.legal_id = vccl.legal_id
         GROUP BY v.body_type, btc.interpretation,v.veh_class,v.legal_id,decode(vccl.legal_id, 2688781,vcc.interpretation, 'PUBLIC BUS(MTRC)')

注：运行环境为PL/SQL DEVELOPER