组由＆＃x2B;选择案例

Question

我尝试查询此代码，但我得到了错误按摩。 “ ORA-00979：不是按表达来的组” 我们可以按函数使用案例总和和最大。

  SELECT PLAN.MFGNO                     "MFGNO",
         PROCESSMASTER.PART_NO          "PART_NO",
         PROCESS.MED_PROC_CD            "M_PROCESS",
         MAX(PLAN.PLAN_START)           "PLAN_START_DATE",
         MAX(PLAN.PLAN_END)             "PLAN_END_DATE",
         MAX(PLAN.ACT_START)            "ACT_START_DATE",
         MAX(PLAN.ACT_END)              "ACT_END_DATE",
         (CASE WHEN PROCESSMASTER.COMP_FLG =1 AND PROCESS.MED_PROC_CD='OUT-P' THEN SUM(SUB_PRO.HACYUKIN) 
         ELSE MAX(SUB_PRO.HACYUKIN)
         END) "SUB_TOTAL_PRICE",
         --SUM(SUB_PRO.HACYUKIN)          "SUB_TOTAL_PRICE",
         MAX(SUB_PRO.SICD)              "SUB_CODE",
         MAX(PROCESSMASTER.PROC_REM) "DE_PROCESS"
  FROM   T_PLANDATA PLAN
         INNER JOIN T_PROCESSNO PROCESSMASTER
                 ON PLAN.BARCODE = PROCESSMASTER.BARCODE
         INNER JOIN T_PLANNED_PROCESS PROCESS
                 ON PROCESSMASTER.PROCESS_CD = PROCESS.PLAN_PROC_CD
         INNER JOIN KEIKAKUMST SUB_PRO
                 ON PROCESSMASTER.BARCODE = SUB_PRO.KMSEQNO
  WHERE PLAN.MFGNO ='T21-F2D1-10034'
  GROUP  BY PLAN.MFGNO,
            PROCESSMASTER.PART_NO,
            PROCESS.MED_PROC_CD;

Answer 1

我们可以按函数使用案例和最大。

是的

，但是您的问题是您使用processMaster.comp_flg = 1和process.med_proc_cd ='out-p'在case> case case expression and no processmaster.comp_flg nor process.med_proc_cd在子句中或聚合函数内部的组中。

您要么想要：

SELECT PLAN.MFGNO                     "MFGNO",
       PROCESSMASTER.PART_NO          "PART_NO",
       PROCESS.MED_PROC_CD            "M_PROCESS",
       MAX(PLAN.PLAN_START)           "PLAN_START_DATE",
       MAX(PLAN.PLAN_END)             "PLAN_END_DATE",
       MAX(PLAN.ACT_START)            "ACT_START_DATE",
       MAX(PLAN.ACT_END)              "ACT_END_DATE",
       (CASE WHEN PROCESSMASTER.COMP_FLG =1 AND PROCESS.MED_PROC_CD='OUT-P' THEN SUM(SUB_PRO.HACYUKIN) 
       ELSE MAX(SUB_PRO.HACYUKIN)
       END) "SUB_TOTAL_PRICE",
       --SUM(SUB_PRO.HACYUKIN)          "SUB_TOTAL_PRICE",
       MAX(SUB_PRO.SICD)              "SUB_CODE",
       MAX(PROCESSMASTER.PROC_REM) "DE_PROCESS"
FROM   T_PLANDATA PLAN
       INNER JOIN T_PROCESSNO PROCESSMASTER
               ON PLAN.BARCODE = PROCESSMASTER.BARCODE
       INNER JOIN T_PLANNED_PROCESS PROCESS
               ON PROCESSMASTER.PROCESS_CD = PROCESS.PLAN_PROC_CD
       INNER JOIN KEIKAKUMST SUB_PRO
               ON PROCESSMASTER.BARCODE = SUB_PRO.KMSEQNO
WHERE PLAN.MFGNO ='T21-F2D1-10034'
GROUP  BY PLAN.MFGNO,
          PROCESSMASTER.PART_NO,
          PROCESS.MED_PROC_CD,
          PROCESSMASTER.COMP_FLG,     -- Add to the group by clause
          PROCESS.MED_PROC_CD         -- Add to the group by clause
;

或类似：

SELECT PLAN.MFGNO                     "MFGNO",
       PROCESSMASTER.PART_NO          "PART_NO",
       PROCESS.MED_PROC_CD            "M_PROCESS",
       MAX(PLAN.PLAN_START)           "PLAN_START_DATE",
       MAX(PLAN.PLAN_END)             "PLAN_END_DATE",
       MAX(PLAN.ACT_START)            "ACT_START_DATE",
       MAX(PLAN.ACT_END)              "ACT_END_DATE",
       CASE
       WHEN MAX(PROCESSMASTER.COMP_FLG) = 1
       AND  COUNT(CASE WHEN PROCESS.MED_PROC_CD = 'OUT-P' THEN 1 END) > 0
       THEN SUM(SUB_PRO.HACYUKIN) 
       ELSE MAX(SUB_PRO.HACYUKIN)
       END "SUB_TOTAL_PRICE",
       --SUM(SUB_PRO.HACYUKIN)          "SUB_TOTAL_PRICE",
       MAX(SUB_PRO.SICD)              "SUB_CODE",
       MAX(PROCESSMASTER.PROC_REM) "DE_PROCESS"
FROM   T_PLANDATA PLAN
       INNER JOIN T_PROCESSNO PROCESSMASTER
               ON PLAN.BARCODE = PROCESSMASTER.BARCODE
       INNER JOIN T_PLANNED_PROCESS PROCESS
               ON PROCESSMASTER.PROCESS_CD = PROCESS.PLAN_PROC_CD
       INNER JOIN KEIKAKUMST SUB_PRO
               ON PROCESSMASTER.BARCODE = SUB_PRO.KMSEQNO
WHERE PLAN.MFGNO ='T21-F2D1-10034'
GROUP  BY PLAN.MFGNO,
          PROCESSMASTER.PART_NO,
          PROCESS.MED_PROC_CD;

注意：这是未经测试的，因为您没有提供表或数据的最小代表性示例。