Oracle Error 00913
Contents |
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie too many values oracle subquery Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN COMPARISON OPERATORS java.sql.sqlexception: ora-00913: too many values DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN pl sql ora-00913 too many values in bulk collect LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle ora-00913 too many values materialized view Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions Oracle / PLSQL: ORA-00913 Error Message Learn the cause and how to resolve the ORA-00913 error message in Oracle. Description When you encounter an ORA-00913 error, the following error message will appear: ORA-00913: too many values Cause You tried to execute a SQL statement that
Ora-00913 Too Many Values In Oracle Insert
required two sets of equal values, but you entered more items in the second set than was in the first set. Resolution The option(s) to resolve this Oracle error are: Option #1 This error often occurs when you are performing a INSERT statement and enter more values in the VALUES clause than the number of columns that you listed. For example, if you executed the following INSERT statement: INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'Microsoft', 'Bill Gates'); In this example, you've chosen to insert values into 2 columns (supplier_id and supplier_name), but you've entered 3 values (1000, Microsoft, and Bill Gates). You need to modify your INSERT statement so there are the same number of columns as there are values. For example: INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'Microsoft'); Option #2 This error can also occur when your subquery in the WHERE clause returns too many columns. For example, if you executed the following SQL statement: SELECT * FROM suppliers WHERE supplier_id > 5000 AND supplier_id IN (SELECT * FROM products WHERE product_name LI
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
Ora-00913 Too Many Values In Case Statement
the company Business Learn more about hiring developers or posting ads with us Stack Overflow oracle too many rows Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 ora-00913 too many values insert all million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up “ORA-00913: too many values” error when I run SQL query up vote 4 down vote favorite I'm getting a https://www.techonthenet.com/oracle/errors/ora00913.php "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 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 http://stackoverflow.com/questions/8353165/ora-00913-too-many-values-error-when-i-run-sql-query 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 199k37361417 asked Dec 2 '11 at 7:40 Coffee 5761962120 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 tables related to person? –Michał Powaga Dec 2 '11 at 7:55 @MichałPowaga - Thanks - yes, it's by a courses_taken table –Coffee Dec 2 '11 at 14:54 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote accepted A quick rewrite: SELECT p.name, p.phone_num
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 http://www.club-oracle.com/threads/getting-error-as-ora-06550-line-5-column-16-pl-sql-ora-00913-too-many-values.11911/ Apps Technical' started by Venkiibm, Jun 3, 2014. Venkiibm Active Member Messages: 14 Likes Received: 0 Trophy Points: 80 hi guys I was trying to insert the table based on the http://forums.databasejournal.com/showthread.php?35557-ORA-00913-too-many-values below select statement and getting error as toomany values .Could you please let me know how to resolve this issue. DROP TABLE Sumtemp; CREATE TABLE Sumtemp ( temp_DIV_LOC_NBR NUMBER(6), temp_RECV_LOC_NBR NUMBER(6), too many 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' 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' too many values 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 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 =
New? Advanced Search Forum Miscellaneous SQL Scripts ORA-00913: too many values If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 2 of 2 Thread: ORA-00913: too many values Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 04-24-2004,01:32 PM #1 cherry0401 View Profile View Forum Posts Registered User Join Date Apr 2004 Posts 3 ORA-00913: too many values Hello, I've been trying to figure out how to fix the above error msg on my UPDATE table statement. Any help will be appreciated.. UPDATE course_stats SET num_sections = (SELECT course_no, COUNT(section_id) FROM section GROUP BY course_no), num_students = (SELECT c.course_no, COUNT(e.section_id) "Total Students" FROM course c, section se, enrollment e WHERE se.section_id = e.section_id AND c.course_no = se.course_no GROUP BY c.course_no), num_instructors = (SELECT c.course_no, COUNT(se.instructor_id) "Total Instructor" FROM course c, section se WHERE c.course_no = se.course_no GROUP BY c.course_no); Name Null? Type COURSE_NO NOT NULL NUMBER(8) DESCRIPTION NOT NULL VARCHAR2(50) COST NUMBER(9,2) PREREQUISITE NUMBER(8) NUM_SECTIONS NOT NULL NUMBER(8) NUM_STUDENTS NOT NULL NUMBER(8) NUM_INSTRUCTORS NOT NULL NUMBER(8) Reply With Quote 04-26-2004,05:24 AM #2 tdkund View Profile View Forum Posts Registered User Join Date Apr 2004 Location Sweden Posts 3 Might be useful to you The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: Check the number of items in each set and change the SQL statement to make them equal. Reply With Quote Quick Navigation SQL Scripts Top Site Areas Settings Private Messages Subscriptions Who's Online Search Forums Forums Home Forums Database Discussions IBM DB2 Informix Microsoft Access Microsoft SQL Server 2008 Microsoft SQL Server 2005 MDX and Analysis Services Reporting Services MS SQL Server 7/MS SQL Server 2000 MySQL Oracle PostgreSQL Sybase Miscellaneous General Database Discussions Ask an Expert Database Design Database Programming Database News and Announcements Structured