Oracle. Using NEXTVAL in an INSERT with GROUP BY

Question

I have the following insert

INSERT INTO DFR_DIARY
(
  SELECT ID_GEN_SEQUENCE.NEXTVAL,
         A.TRANSACTION_REPORTING_UNIT_CD TRECON_RUC,
         A.CYCLE_NUMBER_ID, 
         A.CYCLE_DATE CYCLE_STARTED_DT, 
         A.UNIT_DIARY_NUMBER_ID DIARY_NUMBER_ID,
         'P' STATUS_CODE,
         NULL MISSO_INPUT_ID,
         A.UNIT_DIARY_DATE DIARY_DATE,
         A.UNIT_DIARY_DATE OPENED_DATE,
         A.UNIT_DIARY_DATE CERTIFIED_DATE,
         COUNT(*) TRANS_ACCEPTED_QY,
         0 TRANS_REJECTED_QY,
         'DFR LOAD' CREATEDBY,  
         SYSDATE CREATEDDATE,
         NULL,
         NULL,
         (SELECT ID FROM UDMIPS.DFR_CYCLE 
             WHERE TRECON_RUC = A.TRANSACTION_REPORTING_UNIT_CD AND 
                   CYCLE_NUMBER_ID = A.CYCLE_NUMBER_ID AND
                   CYCLE_STARTED_DT = A.CYCLE_DATE) CYCLE_ID,

         (SELECT ID FROM UDMIPS.DIARY
             WHERE TRECON_RUC = A.TRANSACTION_REPORTING_UNIT_CD AND
                   DIARY_NUMBER = A.UNIT_DIARY_NUMBER_ID AND
                   DIARY_DATE = A.UNIT_DIARY_DATE) DIARY_ID

  FROM UDMIPS.TEMP_TRANSACTION_RESEARCH A
  WHERE 
    A.TRANSACTION_ERROR_CODE IS NULL AND
    A.APPLICATION_SYSTEMS_SOURCE_CD = 'P' AND
    A.TRANSACTION_SERIAL_NUMBER_ID IN
      (SELECT TRANSACTION_SERIAL_ID FROM UDMIPS.DIARY_TRANSACTIONS)
   GROUP BY A.CYCLE_NUMBER_ID, 
         A.CYCLE_DATE, 
         A.TRANSACTION_REPORTING_UNIT_CD,
         A.UNIT_DIARY_NUMBER_ID ,
         A.UNIT_DIARY_DATE
);

This doesn't work. I get ORA-2287 Sequence number not allowed here. If I use a random number like 1 or 2 for all records I am fine. I guess becuase of the grouping it's failing.I do use the same method in other inserts but without group by and it's fine. Is there any way around it. The NEXTVAL generates the primery Key for the records.

score 4 · Answer 1 · answered May 08 '15 at 17:55

You can use subselect:

INSERT INTO DFR_DIARY
(
  SELECT ID_GEN_SEQUENCE.NEXTVAL, sub.*
  FROM (SELECT 
         A.TRANSACTION_REPORTING_UNIT_CD TRECON_RUC,
         A.CYCLE_NUMBER_ID, 
         A.CYCLE_DATE CYCLE_STARTED_DT, 
         A.UNIT_DIARY_NUMBER_ID DIARY_NUMBER_ID,
         'P' STATUS_CODE,
         NULL MISSO_INPUT_ID,
         A.UNIT_DIARY_DATE DIARY_DATE,
         A.UNIT_DIARY_DATE OPENED_DATE,
         A.UNIT_DIARY_DATE CERTIFIED_DATE,
         COUNT(*) TRANS_ACCEPTED_QY,
         0 TRANS_REJECTED_QY,
         'DFR LOAD' CREATEDBY,  
         SYSDATE CREATEDDATE,
         NULL,
         NULL,
         (SELECT ID FROM UDMIPS.DFR_CYCLE 
             WHERE TRECON_RUC = A.TRANSACTION_REPORTING_UNIT_CD AND 
                   CYCLE_NUMBER_ID = A.CYCLE_NUMBER_ID AND
                   CYCLE_STARTED_DT = A.CYCLE_DATE) CYCLE_ID,

         (SELECT ID FROM UDMIPS.DIARY
             WHERE TRECON_RUC = A.TRANSACTION_REPORTING_UNIT_CD AND
                   DIARY_NUMBER = A.UNIT_DIARY_NUMBER_ID AND
                   DIARY_DATE = A.UNIT_DIARY_DATE) DIARY_ID

  FROM UDMIPS.TEMP_TRANSACTION_RESEARCH A
  WHERE 
    A.TRANSACTION_ERROR_CODE IS NULL AND
    A.APPLICATION_SYSTEMS_SOURCE_CD = 'P' AND
    A.TRANSACTION_SERIAL_NUMBER_ID IN
      (SELECT TRANSACTION_SERIAL_ID FROM UDMIPS.DIARY_TRANSACTIONS)
   GROUP BY A.CYCLE_NUMBER_ID, 
         A.CYCLE_DATE, 
         A.TRANSACTION_REPORTING_UNIT_CD,
         A.UNIT_DIARY_NUMBER_ID ,
         A.UNIT_DIARY_DATE
  ) sub
);

Thank you very much, that worked for the sequence, but I get an ORA-00918 Column ambiguously defined. — Ahmad Ghosheh, May 08 '15 at 18:35
ok, Gave it the the name it's suppose to have and that worked, Thank you for all the help. — Ahmad Ghosheh, May 11 '15 at 13:34

Oracle. Using NEXTVAL in an INSERT with GROUP BY

1 Answers1