2 Replies Latest reply: Aug 16, 2014 8:34 PM by Cuauhtemoc Amox RSS

    single-row subquery returns more than one row

    Mr.Jondee

      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_ID,

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

                           SEQ_IC_SCHEMA_LEVEL_MAP_ID.nextval,

                           LEVEL_VALUE,

                           1 ACTIVE_FLG

                         

                   FROM    ic_schema_level

                   WHERE   schema_id = 1000

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

          Hi,

           

          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. 11.2.0.2.0).

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