Group by Select Case-CodePudding

I try to query this code but i got the error massage. "ORA-00979: not a GROUP BY expression" Can we use case SUM and Max in group by function.

  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;

CodePudding user response：

Can we use case SUM and Max in group by function.

Yes

However, your problem is that you use PROCESSMASTER.COMP_FLG =1 AND PROCESS.MED_PROC_CD = 'OUT-P' inside the CASE expression and neither PROCESSMASTER.COMP_FLG nor PROCESS.MED_PROC_CD are in the GROUP BY clause or inside of an aggregation function.

You either want:

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
;

or something like:

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;

Note: this is untested as you have not provided a minimal representative example of your tables or data to test against.