ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在 "QLYWL.FN_MERGEFREIGHTNAME", line 8查询的时候去掉某一个select对象时,就不会出现这样的问题了。
比如select a,b,c from TABLE,这样会有上面的错误,但是select a,c from TABLE,就不会有问题了,为什么呢?
ORA-06512: 在 "QLYWL.FN_MERGEFREIGHTNAME", line 8查询的时候去掉某一个select对象时,就不会出现这样的问题了。
比如select a,b,c from TABLE,这样会有上面的错误,但是select a,c from TABLE,就不会有问题了,为什么呢?
比如
select a,b,(select name from tablea a where a.id=b.id) from tableb b
如果是这样的SQL的话那肯定会报你上面的错的
你要改成这样
select a,b,(select name from tablea a where a.id=b.id and rownum =1 ) from tableb b
from V_FreightDetail
Where 1=1 And ((StartStation='727' And State='待运') Or (State ='在新乡' And EndStation<>'727'))
其中V_FreightDetail是一个视图,上面这个语句执行会出问题,但是去掉FreightName后就不会有问题了。
V_FreightDetail代码:
CREATE OR REPLACE VIEW V_FREIGHTDETAIL AS
SELECT s.PKID,
s.BillID ,
s.BillCode,
s.UsedSerial,
s.SCardNum,
s.SName,
s.SPhone,
s.SMobile,
s.EName,
s.EPhone,
s.EMobile,
s.StartStation,
s.EndStation,
s.DesAddress,
s.SendDate,
s.FillDate,
s.FillName,
s.ISAbnormal,
s.Inform,
s.BackPkID,
s.ISBack,
s.IsDeliver,
s.IsPrint,
s.LandHQDate,
s.State,
s.ISBackTicket,
s.Memo,
s.HandlePerson,
s.GatherPerson,
d.MIndex,
d.YFPay,
d.BFPay,
d.XXFPay,
d.Carriage,
d.DFCarriage,
d.FreightValue,
d.BFRate,
d.Insurance,
d.Deliver,
d.CCZFValue,
d.CCDFValue,
d.XXFValue,
d.Commision,
d.AuditLevel,
d.AppOper,
d.AddDate,
d.AppStation,
d.CheckOper,
d.CheckDate,
Fn_MergeFreightName( s.PKID) AS FreightName,
Fn_MergeFreightNum( s.PKID) AS FreightNum,
Fn_MergeFreightWeight( s.PKID) AS FreightWeight,
Fn_MergePack( s.PKID) AS FreightPack,
s.BillType,
s.BillCheck,
s.BackTicketDate,
s.BackDate,
s.IsLossBill,
s.LossBillDate,
x.StationName AS SAddress,
x.StationName AS EAddress,
s.ExpectDate,
(CASE x.CenterId
WHEN '0' THEN
x.StationId
ELSE
x.CenterId
END) AS StartCenterId,
(CASE x.CenterId
WHEN '0' THEN
x.StationId
ELSE
x.CenterId
END) AS EndCenterId,
(CASE x.CenterId
WHEN '0' THEN
x.StationName
ELSE
Fn_GetStationName(x.CenterId)
END) AS StartCenterName,
(CASE x.CenterId
WHEN '0' THEN
x.StationName
ELSE
Fn_GetStationName( x.CenterId)
END) AS EndCenterName
FROM Freight_Bill s
,CityList x
/*
LEFT OUTER JOIN x
ON s.EndStation = x.StationID
LEFT OUTER JOIN x AS x
ON s.StartStation = x.StationID
*/
,(SELECT
MpkId,
MIndex,
YFPay,
BFPay,
XXFPay,
Carriage,
DFCarriage,
FreightValue,
BFRate,
Insurance,
Deliver,
CCZFValue,
CCDFValue,
XXFValue,
Commision,
AuditLevel,
AppOper,
AddDate,
AppStation,
CheckOper,
CheckDate
FROM Freight_BillPayDetails p
WHERE (MIndex = (SELECT MAX(MIndex) Expr1
FROM Freight_BillPayDetails
WHERE (MpkId = p.MpkId)
AND (AuditLevel = 1)))) d
where
s.PKID = d.MpkId(+)
and s.StartStation = x.StationID(+);
所以加上它就返回多行;
去掉它就返回单行。
Fn_MergeFreightNum( s.PKID) AS FreightNum,
Fn_MergeFreightWeight( s.PKID) AS FreightWeight,
Fn_MergePack( s.PKID) AS FreightPack, 这四列中必定有一个返回超过1行的。不然不会报这样的错。
select a ,b from table1 where a = (select a from table2 where 1=1 )类型这样的,如果 select a from table2 where 1=1 中返回结果 不是一笔记录,就会报你所说的错。因没有详细的SQL语句,无法给出答案,最好把源码贴出来