CODEWITHSUNDEEP
sqloracle-databasesubquerysql-insert
nauanalima
nauanalima

Reputation: 59

ORA-00913 in sql query

I'm using Oracle SQL and I'm trying do run the following query:

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

For the value of data_mapp_info_regs I'm trying to set as 1 if it's NULL and get the previous max value plus one if it already exists for a setted index data_mapp_camp_id. But I get the error ORA-00913: "too many values".

How do I fix this query?

Upvotes: 0

Views: 87

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

The way I see it, the 2nd subquery should be modified to this:

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

Upvotes: 2

NickW
NickW

Reputation: 9818

This part of the query is returning two values, you can only return one value as it corresponds to a single column in the table you are inserting in to:

(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
    ),

Upvotes: 1

Related Questions