sql - sql 查询中的 ORA-00913

Question

我正在使用 Oracle SQL，我正在尝试运行以下查询：

    INSERT
INTO data_mapp_info
  (
    data_mapp_info_id,
    data_mapp_camp_id,
    data_mapp_info,
    data_mapp_info_regs,
    dt_first,
    dt_ult_modf,
    user_ult_modf
  )
  VALUES
  (
    (SELECT MAX(data_mapp_info_id)+1 AS data_mapp_info_id
      FROM MAPP.DATA_MAPP_INFO
    )
    ,
    77,
    'Value 1',
    (SELECT MAX(data_mapp_info_regs),
      CASE data_mapp_info_regs
        WHEN NULL
        THEN 1
        ELSE MAX(data_mapp_info_regs)+1
      END
    FROM MAPP_SOA.DATA_MAPP_info
    WHERE data_mapp_camp_id = 77
    ),
    sysdate,
    sysdate,
    'user.name'
  ) ;

对于data_mapp_info_regs的值，如果它为NULL ，我尝试将其设置为1 ，如果设置的索引data_mapp_camp_id已经存在，则获取前一个最大值加一。但我收到错误ORA-00913: "too many values"。

如何修复此查询？

标签： sqloraclesubquerysql-insert

Answer 1

在我看来，第二个子查询应该修改为：

INSERT INTO data_mapp_info (data_mapp_info_id,
                            data_mapp_camp_id,
                            data_mapp_info,
                            data_mapp_info_regs,
                            dt_first,
                            dt_ult_modf,
                            user_ult_modf)
        VALUES (
                  (SELECT MAX (data_mapp_info_id) + 1 AS data_mapp_info_id
                     FROM MAPP.DATA_MAPP_INFO),
                  77,
                  'Value 1',
                  -- This subquery should be modified
                  (SELECT NVL (MAX (data_mapp_info_regs), 0) + 1
                     FROM MAPP_SOA.DATA_MAPP_info
                    WHERE data_mapp_camp_id = 77),
                  --
                  SYSDATE,
                  SYSDATE,
                  'user.name');