2 Replies Latest reply on Oct 23, 2009 7:10 AM by Frank Kulash

    PL/SQL: ORA-00947: not enough values error message

    729722
      Hi all i am getting Error(25,63): PL/SQL: ORA-00947: not enough values error message when executing following insert statement. I am new to Oracle SPs, so could someone help me to solve the issue.

      insert into estimate (ID, mID, mValue) values('select (case when pm.ID is null then 10
      else pm.ID
      end) ID, m1.mID,(case when mValue < 1 and m1.mID in( 1,7)
      then mValue*100
      else mValue
      end) mValue from
      scott.METRIC m1 left outer join
      scott.PROJECTMETRIC pm on m1.mID = pm.ID and pm.ID = 10)');
        • 1. Re: PL/SQL: ORA-00947: not enough values error message
          Toon Koppelaars
          The syntax to insert rows into a table from a subquery is as follows:
          insert into table (col1, col2, ...)
          select ... , ..., ....
          from ..., ....
          where ....
          /
          • 2. Re: PL/SQL: ORA-00947: not enough values error message
            Frank Kulash
            Hi,

            Welcome to the forum!
            user2281943 wrote:
            Hi all i am getting Error(25,63): PL/SQL: ORA-00947: not enough values error message when executing following insert statement. I am new to Oracle SPs, so could someone help me to solve the issue.

            insert into estimate (ID, mID, mValue) values('select (case when pm.ID is null then 10
            else pm.ID
            end) ID, m1.mID,(case when mValue < 1 and m1.mID in( 1,7)
            then mValue*100
            else mValue
            end) mValue from
            scott.METRIC m1 left outer join
            scott.PROJECTMETRIC pm on m1.mID = pm.ID and pm.ID = 10)');
            You say you are going to set 3 columns of the new row, but you only give one value, a very long string.

            Don't use the keyword VALUES if a query is producing the values to be inserted, and don't enclose the query in quotes.
            I think you meant:
            insert into estimate (ID, mID, mValue) 
            select  (case 
                      when pm.ID is null then 10
                                         else pm.ID
                     end
                 ) ID
            ,      m1.mID
            ,     ( case 
                      when mValue < 1 and m1.mID in( 1,7) 
                            then mValue*100
                            else mValue
                      end
                 ) mValue 
            from             scott.METRIC           m1 
            lleft outer join scott.PROJECTMETRIC      pm      on      m1.mID = pm.ID 
                                          and      pm.ID  = 10
            ;
            1 person found this helpful