Sequence number not allowed here

October 3, 2022

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');

>Solution :

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;