无法对sql query...not求和/分组确定按FISCAL_YEAR分组时失败的原因

Question

当我将group by添加到外部select时，应该能够按FISCAL_YEAR...I求和/分组的简单查询得到一个无效列错误。

SELECT 
    (round((ESC.SUPPLE_RATE * MVAL.MAXKWH),2)) SUP_COST,
    CASE
       WHEN to_number(to_char(to_date(MVAL.cread),'MM')) > 5 
         THEN to_number((to_char(to_date(MVAL.cread),'YYYY')))+1
       else to_number(to_char(to_date(MVAL.cread),'YYYY'))
    END "FISCAL_YEAR"
from 
   ((SELECT 
        to_char(last_day(ADD_MONTHS(SYSDATE, -ROWNUM)), 'DD-MON-YYYY') MONTHS_ 
     FROM 
        DUAL 
     CONNECT BY LEVEL <= 25)
LEFT join
    (SELECT 
        to_char(READING_DATE,'DD-MON-YYYY') cread,MAX(KWH_READING) MAXKWH 
     from 
        ENERGY_METRICS_VDC 
     GROUP BY 
         to_char(READING_DATE,'DD-MON-YYYY')) MVAL on MONTHS_ = MVAL.cread
LEFT JOIN
   ENERGY_SUPPLY_CHARGE ESC on (MONTHS_ between ESC.START_DATE and ESC.END_DATE))
 order by 
    FISCAL_YEAR ASC;

Answer 1

简而言之，Oracle不允许按具有别名的计算字段(在本例中为FISCAL_YEAR)进行分组。解决这个问题的一种方法是将查询包装在另一个查询中，并在“外部”查询中进行分组：

SELECT * FROM (
  SELECT 
      (round((ESC.SUPPLE_RATE * MVAL.MAXKWH),2)) SUP_COST,
      CASE
         WHEN to_number(to_char(to_date(MVAL.cread),'MM')) > 5 
           THEN to_number((to_char(to_date(MVAL.cread),'YYYY')))+1
         else to_number(to_char(to_date(MVAL.cread),'YYYY'))
      END "FISCAL_YEAR"
  from 
     ((SELECT 
          to_char(last_day(ADD_MONTHS(SYSDATE, -ROWNUM)), 'DD-MON-YYYY') MONTHS_ 
       FROM 
          DUAL 
       CONNECT BY LEVEL <= 25)
  LEFT join
      (SELECT 
          to_char(READING_DATE,'DD-MON-YYYY') cread,MAX(KWH_READING) MAXKWH 
       from 
          ENERGY_METRICS_VDC 
       GROUP BY 
           to_char(READING_DATE,'DD-MON-YYYY')) MVAL on MONTHS_ = MVAL.cread
  LEFT JOIN
     ENERGY_SUPPLY_CHARGE ESC on (MONTHS_ between ESC.START_DATE and ESC.END_DATE))
  )
GROUP BY FISCAL_YEAR
order by FISCAL_YEAR ASC;

分享和享受。