ORA-01427single-row subquery returns more than one

今天改bug遇到了一个错误,如题.是别人改了我的sql,没经过严格的测试,后来发现改坏了.出错的语句如下 :

select
g.id, g.account_id, g.account_relationship_id
, (select le.DISPLAY_NAME
from g, lov_element le
where g.GRIEVANCE_TYPE = le.VALUE
and le.LOV_NAME = 'GRIEVANCE_TYPE') grievance_type
, g.requestor_name, g.continue_coverage_flag
, g.comments, g.creation_date, g.approved_flag,
p.first_name,p.id
from
account_relationship ar,g, person p ,account a

产生这个错误是由于子查询中的结果如果 返回结果有两个以上,就会导致oracle抛出这个错,因为查询条件不能支持多个查询参数.可以做如下修改:

select
g.id, g.account_id, g.account_relationship_id
, nvl(lov_list1.DISPLAY_NAME, ' ') as grievance_type
, g.requestor_name, g.continue_coverage_flag
, g.comments, g.creation_date, g.approved_flag, p.first_name, p.middle_initial, p.last_name, p.party_id
from
account_relationship ar,g, person p ,account a,
(SELECT l.lov_name,l.display_name,l.value,enabled from lov_list l where (lov_name='GRIEVANCE_TYPE' and enabled='T')) lov_list1

要点是通过在where条件中加入需要查询的结果子集来将数据库中保存的value替换成页面上显示的display name
阅读更多

错误#1242 - Subquery returns more than 1 row的问题

09-29

这个超级长的sql,报了一个 Subquery returns more than 1 row的错,水平太次,看不出来是哪个地方,大神帮忙看一下rnrn[code=sql]rnSELECTrn tab5.ID,rn tab5.LE_CODE,rn tab5.LE_NAME,rn tab5.US_ID,rn ROUND(tab5.STUDYTIME / 60, 2) AS STUDYTIME,rn tab5.STUDYPLAN,rn tab5.EXAM_ID,rn tab5.PASS_MARK,rn tab5.MARK,rn tab5.mainid,rn tab6.REGNAME,rn tab6. NAME,rn tab6.TC_ID,rn tab6.GROUPNAME,rn tab6.LE_ID AS MAIN_LE_ID,rn tab6.ID AS TUSER_IDrnFROMrn (rn SELECTrn tab2.TC_ID,rn tab2.ID,rn tab2.REGNAME,rn tab2. NAME,rn tab2.GROUPNAME,rn tab2.GROUP_ID,rn le.ID AS LE_IDrn FROMrn (rn SELECT DISTINCTrn tab1.TC_ID,rn tab1.ID,rn tab1.REGNAME,rn tab1. NAME,rn tab1.GROUPNAME,rn tab1.GROUP_IDrn FROMrn (rn SELECTrn tcu.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tp. NAME AS GROUPNAME,rn tp.ID AS GROUP_IDrn FROMrn trainingclass_user tcu,rn tuser tu,rn training_class tc,rn tuser_group tg,rn tgroup tprn WHERErn tcu.TU_ID = tu.IDrn AND tu. STATUS = 1rn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tcu.TC_IDrn AND tg.USER_ID = tu.IDrn AND tp.ID = tg.GROUP_IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tp. NAME AS GROUPNAME,rn tp.ID AS GROUP_IDrn FROMrn tc_array_user tau,rn user_station us,rn tuser tu,rn training_class tc,rn tuser_group tg,rn tgroup tprn WHERErn tau.TYPE = 1rn AND tau.ENTITYID = us.ST_IDrn AND us.US_ID = tu.IDrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tau.TC_IDrn AND tu. STATUS = 1rn AND tg.USER_ID = tu.IDrn AND tp.ID = tg.GROUP_IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tgp. NAME AS GROUPNAME,rn tgp.ID AS GROUP_IDrn FROMrn tc_array_user tau,rn tuser_group tg,rn tuser tu,rn training_class tc,rn tgroup tgprn WHERErn tau.TYPE = 2rn AND tau.ENTITYID = tg.GROUP_IDrn AND tg.USER_ID = tu.IDrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tau.TC_IDrn AND tu. STATUS = 1rn AND tg.GROUP_ID = tgp.IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAME,rn tgp. NAME AS GROUPNAME,rn tgp.ID AS GROUP_IDrn FROMrn tc_array_user tau,rn tuser tu,rn training_class tc,rn tuser_group tg,rn tgroup tgprn WHERErn tau.TYPE = 3rn AND tau.ENTITYID = tu.businessrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tc.ID = tau.TC_IDrn AND tu. STATUS = 1rn AND tg.USER_ID = tu.IDrn AND tgp.ID = tg.GROUP_IDrn ) AS tab1rn WHERErn tab1.ID NOT IN (rn SELECTrn tue.TU_IDrn FROMrn tc_user_except tue,rn training_class tcrn WHERErn tc.ID = tue.TC_IDrn AND tc. STATUS = 1rn AND 1 = 1rn AND tc.ID = 'd0463579-3c09-41cb-9f93-d37c85171eb3'rn AND tue.flag = 0rn )rn ) tab2,rn TGROUP_TRAININGCLASS tt,rn tgroup tgp,rn trainingclass_lesson tl,rn lesson lern WHERErn tab2.TC_ID = tt.TC_IDrn AND tgp.ID = tt.TG_IDrn AND tgp.ID IN (rn +'userid'+rn )rn AND tab2.TC_ID = tl.TC_IDrn AND le.ID = tl.LE_IDrn AND le. STATUS = 1rn ) tab6rnLEFT JOIN (rn SELECTrn tab4.ID,rn tab4.LE_CODE,rn tab4.LE_NAME,rn tab4.US_ID,rn tab4.STUDYTIME,rn tab4.STUDYPLAN,rn tab4.EXAM_ID,rn tab4.PASS_MARK,rn IFNULL(rn (rn SELECT DISTINCTrn em.MARKrn FROMrn exam_mark em,rn exam_stu_paper esprn WHERErn em.STU_ID = tab4.US_IDrn AND em.EXAM_ID = esp.EXAM_IDrn AND esp.STU_ID = tab4.US_IDrn AND em.EXAM_ID = tab4.EXAM_IDrn ),rn 0rn ) AS MARK,rn IFNULL(rn (rn SELECT DISTINCTrn il.mainidrn FROMrn integral ilrn WHERErn il.usid = tab4.US_IDrn AND il.mainid = tab4.IDrn ) ,- 1rn ) AS mainidrn FROMrn (rn SELECT DISTINCTrn tab2.ID,rn tab2.LE_CODE,rn tab2.LE_NAME,rn tab2.US_ID,rn IFNULL(rn (rn SELECT DISTINCTrn uss.STUDYTIMErn FROMrn userstudystatistics ussrn WHERErn uss.LE_ID = tab2.IDrn AND uss.US_ID = tab2.US_IDrn ),rn 0rn ) AS STUDYTIME,rn IFNULL(rn (rn SELECT DISTINCTrn uli.STUDYPLANrn FROMrn usertlidstudyinfo ulirn WHERErn uli.LE_ID = tab2.IDrn AND uli.US_ID = tab2.US_IDrn ),rn 0rn ) AS STUDYPLAN,rn IFNULL(tab3.exam_id, 0) AS EXAM_ID,rn IFNULL(tab3.PASS_MARK, 0) AS PASS_MARKrn FROMrn (rn SELECT DISTINCTrn le.ID,rn le.LE_CODE,rn le.LE_NAME,rn sl.US_IDrn FROMrn selectedlesson sl,rn lesson lern WHERErn sl.LE_ID = le.IDrn AND sl.TYPE = 0rn AND le. STATUS = 1rn UNIONrn SELECTrn le.ID,rn le.LE_CODE,rn le.LE_NAME,rn tab1.US_IDrn FROMrn (rn SELECTrn tcu.TC_ID,rn tu.ID AS US_ID,rn tu.REGNAME,rn tu. NAMErn FROMrn trainingclass_user tcu,rn tuser turn WHERErn tcu.TU_ID = tu.IDrn AND tu. STATUS = 1rn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAMErn FROMrn tc_array_user tau,rn user_station us,rn tuser turn WHERErn tau.TYPE = 1rn AND tau.ENTITYID = us.ST_IDrn AND us.US_ID = tu.IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAMErn FROMrn tc_array_user tau,rn tuser_group tg,rn tuser turn WHERErn tau.TYPE = 2rn AND tau.ENTITYID = tg.GROUP_IDrn AND tg.USER_ID = tu.IDrn UNIONrn SELECTrn tau.TC_ID,rn tu.ID,rn tu.REGNAME,rn tu. NAMErn FROMrn tc_array_user tau,rn tuser turn WHERErn tau.TYPE = 3rn AND tau.ENTITYID = tu.businessrn ) tab1,rn training_class tc,rn trainingclass_lesson tl,rn lesson lern WHERErn tab1.TC_ID = tc.IDrn AND tl.TC_ID = tc.IDrn AND le.ID = tl.LE_IDrn AND tc. STATUS = 1rn AND le. STATUS = 1rn UNIONrn SELECTrn sl.LESSION_ID AS ID,rn le.LE_CODE,rn le.LE_NAME,rn tab1.US_IDrn FROMrn (rn SELECTrn us.ID,rn us.US_ID,rn us.ST_ID,rn sn. NAMErn FROMrn user_station us,rn station snrn WHERErn us.ST_ID = sn.IDrn ) tab1,rn station_lesson sl,rn lesson lern WHERErn sl.STATION_ID = tab1.ST_IDrn AND sl.LESSION_ID = le.IDrn AND le. STATUS = 1rn ) tab2rn LEFT JOIN (rn SELECTrn ei.OUTER_ID,rn ei.ID AS EXAM_ID,rn ei.PASS_MARKrn FROMrn exam_info eirn WHERErn ei.OUTER_TYPE = 1rn ) tab3 ON tab2.ID = tab3.OUTER_IDrn ) tab4rn) tab5 ON tab5.US_ID = tab6.IDrnWHERErn tab6.LE_ID = tab5.IDrnORDER BYrn tab6. NAME,rn tab6.ID,rn tab5.LE_CODErnrn[/code]rnrnrn

出现错误:Subquery returns more than 1 row

05-11

这是一个进销存的出入库项目部分,现在问题不知道出在哪里,请各位大虾帮帮忙,帮忙看看rnrn```rn java.sql.SQLException: Subquery returns more than 1 rowrn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)rn at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)rn at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)rn at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)rn at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)rn at com.sxt.gmms.dao.instorage.InStorageDao.addInStorageAndItem(InStorageDao.java:40)rn at com.sxt.gmms.frame.instorage.instorage.InStorageFrame.actionPerformed(InStorageFrame.java:233)rn at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)rn at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)rn at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)rn at javax.swing.DefaultButtonModel.setPressed(Unknown Source)rn at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)rn at java.awt.Component.processMouseEvent(Unknown Source)rn at javax.swing.JComponent.processMouseEvent(Unknown Source)rn at java.awt.Component.processEvent(Unknown Source)rn at java.awt.Container.processEvent(Unknown Source)rn at java.awt.Component.dispatchEventImpl(Unknown Source)rn at java.awt.Container.dispatchEventImpl(Unknown Source)rn at java.awt.Component.dispatchEvent(Unknown Source)rn at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)rn at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)rn at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)rn at java.awt.Container.dispatchEventImpl(Unknown Source)rn at java.awt.Window.dispatchEventImpl(Unknown Source)rn at java.awt.Component.dispatchEvent(Unknown Source)rn at java.awt.EventQueue.dispatchEventImpl(Unknown Source)rn at java.awt.EventQueue.access$200(Unknown Source)rn at java.awt.EventQueue$3.run(Unknown Source)rn at java.awt.EventQueue$3.run(Unknown Source)rn at java.security.AccessController.doPrivileged(Native Method)rn at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)rn at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)rn at java.awt.EventQueue$4.run(Unknown Source)rn at java.awt.EventQueue$4.run(Unknown Source)rn at java.security.AccessController.doPrivileged(Native Method)rn at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)rn at java.awt.EventQueue.dispatchEvent(Unknown Source)rn at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)rn at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)rn at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)rn at java.awt.EventDispatchThread.pumpEvents(Unknown Source)rn at java.awt.EventDispatchThread.pumpEvents(Unknown Source)rn at java.awt.EventDispatchThread.run(Unknown Source)rnrnrn这是项目的源代码rn/**rn * 商品入库的DAO层rn * rn * @author Administratorrn * rn */rnpublic class InStorageDao rnrn public void addInStorageAndItem(InStorage inStorage,rn List itemList) rn Connection con = null;rn PreparedStatement stat = null;rn ResultSet rs = null;rn try rn con = DBUtil.getConn();rn // 设置自动提交为falsern con.setAutoCommit(false);rn // 先保存入库单rn String inSql = "insert into iss_in(" + "in_code," + "in_date,"rn + "in_status," + "emp_id)" + " values(?,?,?,("rn + "select emp_id from iss_employee "rn + "where emp_name=?)) ";rn stat = con.prepareStatement(inSql, new String[] "in_id" );rn stat.setString(1, inStorage.getInCode());rn stat.setDate(2, new java.sql.Date(inStorage.getInDate().getTime()));rn stat.setInt(3, inStorage.getInStatus());rn stat.setString(4, inStorage.getEmp().getEmpName());rn stat.executeUpdate();//出错了???rnrn // 取得返回的入库单idrn int inId = 0;rn rs = stat.getGeneratedKeys();rn if (rs.next()) rn inId = rs.getInt(1);rn rn // 再保存入库明细rn String itemSql = "insert into iss_in_item(" + "in_item_price,"rn + "in_item_qty," + "in_item_status," + "goods_id,"rn + "in_id)" + "values(?,?,?,"rn + "(select goods_id from iss_goods where goods_code = ?),"rn + "?)";rn stat = con.prepareStatement(itemSql);rn for (InStorageItem inStorageItem : itemList) rn stat.setFloat(1, inStorageItem.getInItemPrice());rn stat.setInt(2, inStorageItem.getInItemQty());rn stat.setInt(3, 1);rn stat.setString(4, inStorageItem.getGoods().getGoodsCode());rn stat.setInt(5, inId);rn stat.executeUpdate();rn rn con.commit();rn catch (Exception e) rn e.printStackTrace();rn try rn con.rollback();rn catch (SQLException e1) rn e1.printStackTrace();rn rn finally rn DBUtil.closeConn(con, stat, rs);rn rn rnrnrn```rnrnrn 这是数据库部分,用的是MySQL数据库,请各位大虾帮帮忙!rn![图片说明](https://img-ask.csdn.net/upload/201605/11/1462948190_737156.png)rnrn![图片说明](https://img-ask.csdn.net/upload/201605/11/1462948205_833102.png)

没有更多推荐了,返回首页