2 Replies Latest reply on Aug 17, 2014 1:34 AM by Cuauhtemoc Amox

    single-row subquery returns more than one row


      Hello , Pls I have a scenario whereby I am trying to insert into a table and will require a subquery/collection /record.


      insert into Table


                           (select SCHEMA_LEVEL_ID from ic_schema_level where SCHEMA_ID in( 101)  )SCHEMA_LEVEL_ID, -- error from this



                           1 ACTIVE_FLG


                   FROM    ic_schema_level

                   WHERE   schema_id = 1000

        • 1. Re: single-row subquery returns more than one row
          Frank Kulash



          What do you want to do?  I can't tell just by looking at code that doesn't do it.


          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.

          In the case of a DML operation (such as INSERT) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g.

          See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

          • 2. Re: single-row subquery returns more than one row
            Cuauhtemoc Amox

            Subqueries used as columns in a select list can not return more than one value, you need to build it in such a way that only one row is returned at most (e.g. using a group_by and a group function) if the used/available criteria is not enough (e.g. active flags, active dates).