How to use a WINDOW function with a CASE statement in Oracle SQL-CodePudding

I am trying to SUM the quantities by groups using a CASE in ORACLE SQL but this does not seem to work:

SUM(A11.NEW_MRP_QTY) OVER (CASE WHEN A11.STOCK_TYPE = 'C' THEN 'INVENTORY'
         WHEN A11.STOCK_TYPE = 'L' THEN 'INTRANSIT PO'
         WHEN A11.STOCK_TYPE = 'B' THEN 'PO AT FACTORY'
         WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE = 'CO') THEN 'PO AT FACTORY'
         WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE <> 'CO') THEN 'NOT BOUGHT'
         WHEN A11.STOCK_TYPE = ' ' THEN 'BLANK STOCK TYPE'
         ELSE 'CHECK')
    END AS 'SUPPLY_QTY'

Any ideas on what I am doing wrong? Thanks.

CodePudding user response：

This is what you have now (somewhat simplified & applied to Scott's sample emp table, as I don't have your table(s) nor you posted any sample data):

SQL> select sum(sal) over (case when deptno = 10 then 'A'
  2                             when deptno = 20 then 'B'
  3                             else 'C'
  4                        end) as supply_qty
  5  from emp;
select sum(sal) over (case when deptno = 10 then 'A'
                      *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>

Why error? Because over part of the function is wrong. It can't have just that case expression, you're missing partition by and/or order by (see line #1), such as

SQL> select sum(sal) over (partition by case when deptno = 10 then 'A'
  2                                          when deptno = 20 then 'B'
  3                                          else 'C'
  4                                     end) as supply_qty
  5  from emp;

SUPPLY_QTY
----------
      8750
      8750
      8750
     10875
     10875
     10875
     10875
     10875
      9400
      9400
      9400
      9400
      9400
      9400

14 rows selected.

SQL>

I don't know what you're about to do here, but above should at least point you to the right direction (as well as comments people posted).