?
Solved

ora-00913 error during inner join

Posted on 2007-08-09
2
Medium Priority
?
521 Views
Last Modified: 2013-12-18
Oracle 10

I dont understand the ora-00913 error Im getting.
Im trying to do an insert into one table from a duplicate table, limited by an inner join
And I get an 0ra-00913 error. But when I do the insert without the join it works.
What am I missing?

Thanks

 STATEMENT WITH INNER JOIN:
SQL> INSERT INTO RT_TEST.MBR_OTHER_SUB_ASSN
  2  SELECT * FROM MVPPEPT.MBR_OTHER_SUB_ASSN C
  3  INNER JOIN MVPPEPT.COB_MEMBER P ON P.CONTRACT_ID = C.FK_CB_CONTRACT_ID ;

INSERT INTO RT_TEST.MBR_OTHER_SUB_ASSN
                     *
ERROR at line 1:
ORA-00913: too many values

 STATEMENT WITH OUT INNER JOIN:
SQL> INSERT INTO RT_TEST.MBR_OTHER_SUB_ASSN
  2   SELECT * FROM MVPPEPT.MBR_OTHER_SUB_ASSN C;

3 rows created.

 
0
Comment
Question by:rmtye
  • 2
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 375 total points
ID: 19664110
Select * attempts to insert columns from both joined tables. You need to limit it to just one of the tables so that you are inserting the right number of columns:

Select TABLENAME.* FROM...
0
 
LVL 42

Expert Comment

by:dqmq
ID: 19664127
OK, I see now:

 INSERT INTO RT_TEST.MBR_OTHER_SUB_ASSN
   SELECT C.* FROM MVPPEPT.MBR_OTHER_SUB_ASSN C
  INNER JOIN MVPPEPT.COB_MEMBER P ON P.CONTRACT_ID = C.FK_CB_CONTRACT_ID ;


Or, if you are mixing columns from the two tables, then you need to name them explicitly in the SELECT list.



0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question