I am struggling as to why the commented out code below (AND ESIB.UNIT_OF_ISSUE IS NULL) is erroring with the error – ORA-00979: not a GROUP BY expression when I uncomment this additional condition in the CASE statement. Both UNIT_OF_ISSUE and PRIMARY_UOM_CODE are both String type fields. If I just use WHEN ESIB.PRIMARY_UOM_CODE = 'EA' it works, so I don’t understand why the AND... condition is causing issues.

I realize I can add the entire case expression to the GROUP BY but I would like to see if there are any other options here, and why this is occuring.

SELECT ESIB.ITEM_NUMBER, 
       ESIT.LONG_DESCRIPTION, 
       IOP.ORGANIZATION_CODE, 
       isub.secondary_inventory SUBINVENTORY_CODE, 
       CASE WHEN SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY) IS NULL 
       THEN  0         
       WHEN ESIB.PRIMARY_UOM_CODE = 'EA' --AND ESIB.UNIT_OF_ISSUE IS NULL
       THEN SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY)          
       ELSE SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY) / INTRACONV.CONVERSION_RATE  
       END ONHAND_QTY
      ,ESIB.PRIMARY_UOM_CODE ONHAND_UOM
FROM   EGP_SYSTEM_ITEMS_B ESIB
       INNER JOIN EGP_SYSTEM_ITEMS_TL ESIT
       ON ESIB.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID 
          AND ESIB.ORGANIZATION_ID = ESIT.ORGANIZATION_ID 
       INNER JOIN INV_ORG_PARAMETERS IOP
       ON ESIB.ORGANIZATION_ID = IOP.ORGANIZATION_ID
          AND INVENTORY_FLAG = 'Y' 
          AND isub.organization_id = iop.organization_id 
GROUP BY
       ESIB.ITEM_NUMBER, 
       ESIT.LONG_DESCRIPTION, 
       IOP.ORGANIZATION_CODE, 
       isub.secondary_inventory, 
       ESIB.PRIMARY_UOM_CODE,
        INTRACONV.CONVERSION_RATE 

>Solution :

ESIB.UNIT_OF_ISSUE is not in the GROUP BY clause:

Some options are:

1. Add ESIB.UNIT_OF_ISSUE to the GROUP BY clause.
2. Wrap ESIB.UNIT_OF_ISSUE in an aggregation function (such as MAX, MIN, COUNT etc.):

   CASE
   WHEN SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY) IS NULL 
   THEN 0         
   WHEN ESIB.PRIMARY_UOM_CODE = 'EA'
   AND  MAX(ESIB.UNIT_OF_ISSUE) IS NULL
   THEN SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY)          
   ELSE SUM(IOQD.PRIMARY_TRANSACTION_QUANTITY)
         / INTRACONV.CONVERSION_RATE
   END AS ONHAND_QTY