Oracle Sql Error 1427

SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted!

Single-row Subquery Returns More Than One Row Oracle Update

Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson ora-01427 single-row subquery returns in update statement Blog

Ora 01427 In Update Statement

returns more than one row tips Oracle Error Tips by Burleson Consulting (S. Karam) The Oracle docs note this on the ORA-01427 error*: ORA-01427 ora-01427 update single-row subquery returns more than one row Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row. Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or single row subquery returns more than one row while updating reword the query so only one row is retrieved. On Oracle Server and PL/SQL User Forum, Adam Wilbur received an ORA-01427 error code and received this information: Question Posted by 'Adam Wilbur' on April 21, 2001 at 20:37:00 EST: Here's what I am trying to do... UPDATE TABLEA SET TABLEA.COLUMN1 = (select TABLEB.COLUMN1 FROM TABLEA, TABLEB WHERE TABLEB.COLUMN2 = 'String Value' AND TABLEA.COLUMN3 = TABLEB.COLUMN3 AND TABLEA.COLUIMN4 = TABLEB.COLUMN4 AND TABLEB.VALUENAME = 'String Value'); This query returns multiple rows, all of which I need to update in TABLEA. How do I do this without getting ORA-01427 error? Answer Dmytro offered this advice to resolve ORA-01427: Try to add and rownum=1 to your subquery conditions if you DO NOT care about the value from the list or DO sure that they are the same. Single Row Subquery A single row subquery returns only one row. It can be used with the equal comparison operators (=,<,>,<>, etc). SQL> s

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the

Ora-01427 Update Multiple Rows

workings and policies of this site About Us Learn more about multiple row subquery in oracle Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions

Single Row Subquery Returns Multiple Rows In Update Statement

Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. http://www.dba-oracle.com/t_ora_01427_single_row_subquery_returns_more_than_one_row.htm Join them; it only takes a minute: Sign up How to fix Ora-01427 single-row subquery returns more than one row in select? up vote 4 down vote favorite When i execute the following query, i get the message like "Ora-01427 single-row subquery returns more than one row" SELECT E.I_EmpID AS EMPID, E.I_EMPCODE AS EMPCODE, E.I_EmpName AS EMPNAME, REPLACE(TO_CHAR(A.I_REQDATE, 'DD-Mon-YYYY'), http://stackoverflow.com/questions/21397694/how-to-fix-ora-01427-single-row-subquery-returns-more-than-one-row-in-select ' ', '') AS FROMDATE, REPLACE(TO_CHAR(A.I_ENDDATE, 'DD-Mon-YYYY'), ' ', '') AS TODATE, TO_CHAR(NOD) AS NOD, DECODE(A.I_DURATION, 'FD', 'FullDay', 'FN', 'ForeNoon', 'AN', 'AfterNoon') AS DURATION, L.I_LeaveType AS LEAVETYPE, REPLACE(TO_CHAR((SELECT C.I_WORKDATE FROM T_COMPENSATION C WHERE C.I_COMPENSATEDDATE = A.I_REQDATE AND C.I_EMPID = A.I_EMPID), 'DD-Mon-YYYY'), ' ', '') AS WORKDATE, A.I_REASON AS REASON, AP.I_REJECTREASON AS REJECTREASON FROM T_LEAVEAPPLY A INNER JOIN T_EMPLOYEE_MS E ON A.I_EMPID = E.I_EmpID AND UPPER(E.I_IsActive) = 'YES' AND A.I_STATUS = '1' INNER JOIN T_LeaveType_MS L ON A.I_LEAVETYPEID = L.I_LEAVETYPEID LEFT OUTER JOIN T_APPROVAL AP ON A.I_REQDATE = AP.I_REQDATE AND A.I_EMPID = AP.I_EMPID AND AP.I_APPROVALSTATUS = '1' WHERE E.I_EMPID <> '22' ORDER BY A.I_REQDATE DESC when i execute this without ORDER BY A.I_REQDATE DESC it returns 100 rows... sql oracle share|improve this question edited Jan 28 '14 at 5:44 asked Jan 28 '14 at 5:37 Sam1604 1,1502819 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote accepted Use the following query: SELECT E.I_EmpID AS EMPID, E.I_EMPCODE AS EMPCODE, E.I_EmpName AS EMPNAME, REPLACE(TO_CHAR(A.I_REQDATE, 'DD-Mon-YYYY'), ' ', '') AS FROMDATE, REPLACE(TO_CHAR(A.I_ENDDATE, 'DD-Mon-Y

CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work https://community.oracle.com/thread/702761 correctly without it enabled. Please turn JavaScript back on and http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/ora01427-singlerow-subquery-returns-more-than-one-row-4812873 reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. More discussions in Data Integrator All PlacesBusiness IntelligenceSystem Management and IntegrationData Integrator This discussion is archived row subquery 10 Replies Latest reply on Jan 29, 2011 12:57 PM by Himanshu Binjola Query in KM 632265 Sep 1, 2008 10:34 AM Hi All, I have table with 5000 records, I need to update some columns that has the UPDATED. So I used the same file row subquery returns as Source as well Target , In source I had a join with a table. I used oracle Increemental update (IKM) , LKM (SQL to SQL) Intially I was getting Unique key index error, So I copied the IKM and enabled the Ignore Errors options in the KM. Then It did not throw any Errors. ( Whether it is a good way of going.....!!!!) But Now the Issue I am facing is when I run the Interface I am getting *1427 : 21000 : java.sql.SQLException: ORA-01427: single-row subquery returns more than one row* java.sql.SQLException: ORA-01427: single-row subquery returns more than one row at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185) at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057) at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java) at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java) at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java) at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java) at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java) at com.sunopsis.dwg.cmd.DwgCommandBase.execute(Dwg

Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries VoIP_News Inside-ERP MacProTX Inside-CRM I_am_the_dragon maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich Locutus Dennis Stevenson DukeGanote Richard iudithm mircea_luca Clinton Jones bracke Nikki Klein AbhaiTripathi Iqbalyk Adrian_Grigoriu bluesguyAZ59 numbersguyPA COMPANIES EdgeWave Sophos Pivotal CRM Wave Direct View All Topics View All Members View All Companies Toolbox for IT Topics Oracle Groups Ask a New Question Oracle Database This group is where peers share technical expertise, solve problems, and discuss issues related to the use of Oracle Databases, including Oracle Grid. Home | Invite Peers | More Oracle Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. ORA-01427: Single-row Subquery Returns More Than One Row kool999 asked Jun 13, 2012 | Replies (20) I tried to execute this on oracle: update sn_voucher_d set payer_n = (select a.account_link_code_n from stg_lgsm_service_mast a, sn_voucher_d c where a.mobl_num_voice_v= c.subscriber_id) it gives me the following error ORA-01427: single-row subquery returns more than one row anyone can help? Join this group Best Answer Updated html error Use In instead of '=' or in the subquery use MAX-MIN Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author. Edits are subject to review by community moderators. Original answer by Sonia Lichtenzveig Jun 13, 2012 Contributors: Top 1 membervote Use In instead of '=' or in the subquery use MAX-MIN Reply from Sonia Lichtenzveig |