Oracle.将NextVal与组一起插入

Question

我有以下插入

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

这不起作用。我在这里获得ORA-2287序列号。如果我使用像1或2这样的随机数作为所有记录，我很好。我猜是因为分组失败。我在其他插入物中使用相同的方法，但没有组，这很好。有什么办法。NextVal生成了记录的引物密钥。

Answer 1

您可以使用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
);