Error Message Ora-00913 Too Many Values
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company too many values oracle subquery Business Learn more about hiring developers or posting ads with us Stack Overflow Questions java.sql.sqlexception: ora-00913: too many values Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, pl sql ora-00913 too many values in bulk collect just like you, helping each other. Join them; it only takes a minute: Sign up SQL Error: ORA-00913: too many values up vote 6 down vote favorite Two tables are identical in terms of table name,
Ora-00913 Too Many Values Materialized View
column names, datatype and size. These tables are located in separate databases, but I am use to current Log in in hr user. insert into abc.employees select * from employees where employee_id=100; I can not give use original query from corporate office. Error starting at line 1 in command: insert into abc.employees select * from employees where employee_id=100; Error at Command Line:1 Column:25 Error report: SQL Error: ORA-00913: too many values 00913. 00000 - ora-00913 too many values in case statement "too many values" *Cause: *Action: sql oracle share|improve this question edited Jan 23 '14 at 10:37 Kent Pawar 79211125 asked Sep 11 '13 at 9:25 user2703444 46123 add a comment| 4 Answers 4 active oldest votes up vote 4 down vote You should specify column names as below. It's good practice and probably solve your problem insert into abc.employees (col1,col2) select col1,col2 from employees where employee_id=100; EDIT: As you said employees has 112 columns (sic!) try to run below select to compare both tables' columns select * from ALL_TAB_COLUMNS ATC1 left join ALL_TAB_COLUMNS ATC2 on ATC1.COLUMN_NAME = ATC1.COLUMN_NAME and ATC1.owner = UPPER('2nd owner') where ATC1.owner = UPPER('abc') and ATC2.COLUMN_NAME is null AND ATC1.TABLE_NAME = 'employees' and than you should upgrade your tables to have the same structure. share|improve this answer edited Sep 11 '13 at 11:14 answered Sep 11 '13 at 9:26 Parado 19.1k73161 Not is possible because original tables in 112 columns and work on large table 800+ so .................. –user2703444 Sep 11 '13 at 9:32 employees table has 112 columns !? –Parado Sep 11 '13 at 9:33 SHOW TO 124317 ROWS –user2703444 Sep 11 '13 at 10:06 @user2703444 I've forgot AND ATC1.TABLE_NAME = 'employees' condition :), try it now please –Parado Sep 11 '13 at 10:08 Usually,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this oracle too many rows site About Us Learn more about Stack Overflow the company Business Learn more
Ora-00913 Too Many Values In Oracle Insert
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Ora 00913 Too Many Values In Merge Statement
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up “ORA-00913: too http://stackoverflow.com/questions/18737369/sql-error-ora-00913-too-many-values many values” error when I run SQL query up vote 4 down vote favorite I'm getting a "too many values" error and I'm not sure how to approach it. The problem starts at the first nested SELECT SELECT p.name, p.phone_number FROM person p WHERE (SELECT ks.title, ks.ks_code FROM required_skills rs JOIN knowledge_skills ks USING (ks_code) WHERE rs.pos_code = 'CS1') IN (SELECT ks.title, ks.ks_code FROM courses_taken http://stackoverflow.com/questions/8353165/ora-00913-too-many-values-error-when-i-run-sql-query ct JOIN course_learning_units clu USING (c_code) JOIN learning_unit_skills lus USING (lu_code) JOIN knowledge_skills ks USING (ks_code)) I looked up info about this error and I believe it's because the subquery in the WHERE returns too many columns. The table data is below. But how can I whittle that down? I appreciate any pointers, Thanks required_skills POS_CODE VARCHAR2(10 BYTE) Yes 1 KS_CODE VARCHAR2(10 BYTE) Yes 2 learning_unit_skills LU_CODE VARCHAR2(10 BYTE) Yes 1 KS_CODE VARCHAR2(10 BYTE) Yes 2 person PER_ID NUMBER(38,0) No 1 NAME VARCHAR2(255 BYTE) Yes 2 STREET VARCHAR2(255 BYTE) Yes 3 CITY VARCHAR2(60 BYTE) Yes 4 ZIP_CODE VARCHAR2(60 BYTE) Yes 5 PHONE_NUMBER VARCHAR2(255 BYTE) Yes 6 GENDER VARCHAR2(6 BYTE) Yes 7 course learning units C_CODE VARCHAR2(10 BYTE) Yes 1 LU_CODE VARCHAR2(10 BYTE) Yes courses taken C_CODE VARCHAR2(10 BYTE) Yes 1 PER_ID NUMBER(38,0) Yes 2 sql oracle oracle11g ora-00913 share|improve this question edited Feb 12 '13 at 5:16 OMG Ponies 199k36356415 asked Dec 2 '11 at 7:40 Coffee 5461962120 in your query after the first where condition there is no field or column is mentioned –Rashmi Kant Shrivastwa Dec 2 '11 at 7:46 1 How are your other ta
and also get a Premium Badge! Getting error as ORA-06550: line 5, column 16: PL/SQL: ORA-00913: too many values Discussion in 'Oracle Apps Technical' started by Venkiibm, Jun 3, 2014. Venkiibm Active Member Messages: 14 http://www.club-oracle.com/threads/getting-error-as-ora-06550-line-5-column-16-pl-sql-ora-00913-too-many-values.11911/ Likes Received: 0 Trophy Points: 80 hi guys I was trying to insert the table based on the below select statement and getting error as toomany values .Could you please let me know how to resolve this http://www.orafaq.com/forum/t/52860/ issue. DROP TABLE Sumtemp; CREATE TABLE Sumtemp ( temp_DIV_LOC_NBR NUMBER(6), temp_RECV_LOC_NBR NUMBER(6), temp_ZL_DIVN_NBR number(5), temp_var1 Number(01)); Declare temp_var number (01); Begin LOOP inseRt INTO sumtemp (temp_DIV_LOC_NBR,temp_RECV_LOC_NBR,temp_ZL_DIVN_NBR,temp_var1) SELECT RQ.DIV_LOC_NBR,rq.RECV_LOC_NBR,rq.ZL_DIVN_NBR, CASE when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'N' too many AND EXCLUDED_F ='N' THEN 1 END AS TEMP_VAR, CASE when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'Y' AND EXCLUDED_F ='N' THEN 2 END AS TEMP_VAR, case when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'N' AND EXCLUDED_F ='Y' THEN 3 END AS TEMP_VAR, case when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'Y' AND EXCLUDED_F ='Y' THEN 4 END AS TEMP_VAR, case when rq.COLLATERAL_UNITS > 0 and rq.COLLATERAL_F = 'Y'and FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN too many values 5 END AS TEMP_VAR, -- SUM(RQ.CALC_PHYSICAL_CNTR_COUNT) AS CARTON_UNTS , -- SUM(RQ.TTL_BOOKED_QTY) AS TTL_BK_QTY FROM RCPT_QTY rq where rq.div_loc_nbr =12 GROUP by rq.DIV_LOC_NBR, rq.RECV_LOC_NBR, RQ.RCPT_MTHD_CD, TEMP_VAR; END LOOP; End; Venkiibm, Jun 3, 2014 #1 rajenb Forum Expert Messages: 361 Likes Received: 114 Trophy Points: 655 Location: Mauritius Hi, In your INSERT statement (and table), you have 4 columns only: inseRt INTO sumtemp (temp_DIV_LOC_NBR,temp_RECV_LOC_NBR,temp_ZL_DIVN_N BR,temp_var1) Click to expand... while in your SELECT statement you have 8 columns (and may be more if I include the 2 commented lines): Code (SQL): SELECT RQ.DIV_LOC_NBR, -- col1 rq.RECV_LOC_NBR, -- col2 rq.ZL_DIVN_NBR, -- col3 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 1 END AS TEMP_VAR, -- col4 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'Y' AND EXCLUDED_F ='N' THEN 2 END AS TEMP_VAR, -- col5 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'N' AND EXCLUDED_F ='Y' THEN 3 END AS TEMP_VAR, -- col6 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'Y' AND EXCLUDED_F ='Y' THEN 4 END AS TEMP_VAR, -- col7 CASE WHEN rq.COLLATERAL_UNITS > 0 AND rq.COLLATERAL_F = 'Y'AND FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 5 END AS TEMP_VAR, -- col8 -- SUM(RQ.CALC_PHYSICAL_CNTR_COUNT) AS CARTON_UNTS , -- SUM(RQ.TTL_BOOKED_QTY) AS TTL_BK_QTY You need to have exactly the same number of columns in both statements. At
ORA-00913: too many values error trying to update 25,000 records [message #140569] Tue, 04 October 2005 16:05 sweetctsour Messages: 4Registered: October 2005 Junior Member I did the following update: UPDATE bob x SET mouse.rr = 'z' WHERE mouse.rr = 'm' AND mouse.agent in (SELECT agent FROM bob x WHERE mouse.ff NOT IN (SELECT cat.gg FROM sue WHERE mouse.ff = cat.gg) AND mouse.rr = 'm') after the update was executed, I got the error ORA-00913: too many values. I got the error because I was updating about 25, 000 records. Someone told me that you can only update about 1,000 records at a time. Therefore I was wandering was there another way to update these records. FYI I know that I can do an update manually. What I mean by update manually is like this: UPDATE bob x SET mouse.rr = 'I' WHERE mouse.rr = 'A' AND mouse.agent IN ('1','2','3','4') but I am trying to keep from writing approximately 25 update statements, each with 1,000 numbers. Please help, Thanks in advance =) Report message to a moderator Re: ORA-00913: too many values error trying to update 25,000 records [message #140576 is a reply to message #140569] Tue, 04 October 2005 17:11 dmitry.nikiforov Messages: 723Registered: March 2005 Senior Member Would be better to provide us with your data structures and sample dates. Rgds. Report message to a moderator Re: ORA-00913: too many values error trying to update 25,000 records [message #140782 is a reply to message #140576] Wed, 05 October 2005 10:21 sweetctsour Messages: 4Registered: October 2005 Junior Member Everything is in a table. The datatypes are as follows: SpecificNumber VARCHAR2(11) Primary Key AnotherNumber NUMBER (10) ThirdNumber VARCHAR2(10) Rank VARCHAR2(1) Rank is what I have to change. Sample data is as follows: Beginning input: 00000000000 111 2222 B 11111111111 333 4444 B 22222222222 555 6666 B 33333333333 777 8888 B 44444444444 999 1010 B 55555555555 123 1212 B 66666666666 456 1313 B Ending output: 00000000000 111 2222 C 11111111111 333 4444 C 22222222222 555 6666 C 33333333333 777 8888 C