Sequence number not allowed here-CodePudding

I need to use group by and nextval of a sequence at the same time. How can I manage that? I am not sure how to modify the query to make it work

  SELECT BCS_RPT_AGGREGATOR_COSTCENTER_seq.nextval AS ID,
    TO_DATE(TO_CHAR(brt.TRANSACTION_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD'),
           brt.COST_CENTER,
           COUNT(brt.COST_CENTER),
           SUM(brt.COLLECTION_AMOUNT),
           SUM(brt.COMMISSION_AMOUNT),
           SUM(brt.SERVICE_FEE),
           SUM(brt.BANK_INCOME_AMOUNT)
    FROM  BCS_RPT_TRANSACTION brt
    WHERE brt.IS_AGGREGATED_C =0
    AND brt.COST_CENTER = :costCenter   GROUP BY brt.COST_CENTER,
             TO_DATE(TO_CHAR(brt.TRANSACTION_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD');

CodePudding user response：

You can't use sequence in GROUP BY queries. You can edit your query as follows with using subquery:

SELECT
    bcs_rpt_aggregator_costcenter_seq.NEXTVAL AS id,
    a.*
FROM
    (
        SELECT
            to_date(to_char(brt.transaction_date, 'YYYY-MM-DD'), 'YYYY-MM-DD'),
            brt.cost_center,
            COUNT(brt.cost_center),
            SUM(brt.collection_amount),
            SUM(brt.commission_amount),
            SUM(brt.service_fee),
            SUM(brt.bank_income_amount)
        FROM
            bcs_rpt_transaction brt
        WHERE
                brt.is_aggregated_c = 0
            AND brt.cost_center = :costcenter
        GROUP BY
            brt.cost_center,
            to_date(to_char(brt.transaction_date, 'YYYY-MM-DD'), 'YYYY-MM-DD')
    ) a;

CodePudding user response：

Simplified, this is what you have:

SQL> create sequence seq;

Sequence created.

SQL> select seq.nextval,
  2         deptno,
  3         sum(sal) sum_sal
  4  from emp
  5  group by deptno;
select seq.nextval,
           *
ERROR at line 1:
ORA-02287: sequence number not allowed here

One option is to use current query (without the sequence) as a subquery (or a CTE, as I did in the following example), and then - in the main select - use data you collected and add the sequence number:

SQL> with temp as
  2    (select deptno,
  3            sum(sal) sum_sal
  4     from emp
  5     group by deptno
  6    )
  7  select seq.nextval,
  8         deptno,
  9         sum_sal
 10  from temp;

   NEXTVAL     DEPTNO    SUM_SAL
---------- ---------- ----------
         1         30       9400
         2         20      10875
         3         10       8750

SQL>

Your code would then look like this:

WITH
   temp
   AS
      (  SELECT TO_DATE (TO_CHAR (brt.transaction_date, 'YYYY-MM-DD'),
                         'YYYY-MM-DD'),
                brt.cost_center,
                COUNT (brt.cost_center),
                SUM (brt.collection_amount),
                SUM (brt.commission_amount),
                SUM (brt.service_fee),
                SUM (brt.bank_income_amount)
           FROM bcs_rpt_transaction brt
          WHERE     brt.is_aggregated_c = 0
                AND brt.cost_center = :costcenter
       GROUP BY brt.cost_center,
                TO_DATE (TO_CHAR (brt.transaction_date, 'YYYY-MM-DD'),
                         'YYYY-MM-DD'))
SELECT bcs_rpt_aggregator_costcenter_seq.NEXTVAL AS id, 
       t.*
  FROM temp t;