消息512，级别16，状态1，过程track_order1，行8子查询返回多个值。子查询遵循=，！=，<，< =，>，> =或子查询用作...时不允许这样做 [英] Msg 512, Level 16, State 1, Procedure track_order1, Line 8 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as...

问题描述

  ALTER   procedure  [dbo]。[track_order1] 
  @ grnid   int ，
  @ insid   int ，
  @ piid   int  
  as  
  begin  
 选择  distinct  pii.inst_id，pd.PIid，pd.title，pd.publisher，pd.format，pd.subscription_period_from  as  Sub_from，
 pd.subscription_period_to  as  sub_to，pd.Qty_in_year  AS 频率
，（选择  distinct （volumn）< span class =code-keyword>来自 Stock_Dispatch  where  inst_id = @ insid 和 PIid = @ piid 和 title = pd.title）作为 Volumn， Coalesce （（ Select  Sum（cast（Dispatch_Qty  as   int ））
 来自 dbo.Stock_Dispatch 其中​​ inst_id = @ insid 和 PIid = @ piid 和 
 title = pd.title 和 order_Status = 2）， 0 ） as  Rec_qtys，
 pd.Qty_in_year -Coalesce（（ Select  Sum（cast（Dispatch_Qty  as   int ））
 来自 dbo.Stock_Dispatch 其中 inst_id = @ insid 和 PIid = @ piid 和 
 title = pd.title 和 order_Status = 2）， 0 ） as  rem，
  Coalesce （（选择 Sum（cast（Dispatch_Qty  as   int ））来自 
 dbo。 Stock_Dispatch 其中 inst_id = @ insid 和 PIid = @ piid 和 title = pd.title 和 order_Status = 1）， 0 ） as  New_dispatch，
  case  pd.Qty_in_year -Coalesce（（ Select  Sum（cast（Dispatch_Qty  as   int ））
 来自 dbo.Stock_Dispatch 其中 inst_id = @ insid 和 PIid = @ piid 和 
 title = pd.title < span class =code-keyword>和 order_Status = 2）， 0 ）何时  0  然后 ' 完成' 
  else  ' 未完成 结束  as  Order_stat 
 来自 dbo.Performa_details pd，Client_Goods_details_master po 
，Stock_Dispatch sd  right  外部 加入 Pe rforma_details pdio   pdio.PIid = sd.PIid，
 Performa_invoice pii 其中 po.PIid = pd.PIid 和 pd.PIid = pii.PI_id 
 和 Grnid = @ grnid 和 pii.inst_id=@insid 
  end  

这个代码中的问题在哪里我无法猜测请给我一个建议谢谢一个分配frd

解决方案

消息是什么问题。您的一个（或多个）子查询返回多行，其中包含预期的单行。除非我们有权访问您的数据库，否则任何寄宿生都很难说出真正的原因。可以识别的方法是对所有子查询进行注释并取消注释，并查看哪一个导致问题。然后，您需要根据业务需要修改查询，以便返回单行。祝你好运。

继续解决方案1 ​​... ArunRajendra完全正确地描述了你应该采取的方法来找到问题...



大多数子查询返回 sum 的东西......除了这一个

（从Stock_Dispatch中选择distinct（volumn）其中inst_id = @insid和PIid = @ piid和title = pd.title）作为Volumn 

...我怀疑是你问题的原因。


另一种让自己更轻松生活的方法是使用空格...空格，制表符，换行符...这是你用一些空格重新格式化的未改变的代码，使其更具可读性......

 ALTER程序[dbo]。[track_order1] 
 @ grnid int，
 @insid int，
 @piid int 
 as 
 begin 
选择distinct pii.inst_id，pd.PIid，pd.title，pd.publisher，pd.format，pd.subscription_period_from as Sub_from，pd.subscription_period_to as sub_to，pd.Qty_in_year AS Frequency，
（
 Select distinct（来自Stock_Dispatch的
，其中inst_id = @ insid和PIid = @ piid和title = pd.title 
）作为Volumn，
 Coalesce（
（
选择总和）从dbo.Stock_Dispatch转换（Dispatch_Qty为int））其中inst_id = @insid和PIid = @ piid 
和title = pd.title和order_Status = 2 
），0）作为Rec_qtys，
 pd.Qty_in_year  -  Coalesce（
（
选择Sum（cast（Dispatch_Qty as int））
来自dbo.Stock_Dispatch，其中inst_id = @insid和PIid = @ piid和title = pd.title和order_Status = 2 
），0）as rem，
 
 Coalesce（
（
选择Sum（cast（Dispatch_Qty as int））来自
 dbo。 Stock_Dispatch其中inst_id = @insid和PIid = @ piid和title = pd.title和order_Status = 1 
），0）as New_dispatch，
 case pd.Qty_in_year  -  Coalesce（
（$ b） $ b选择Sum（cast（Dispatch_Qty as int））
来自dbo.Stock_Dispatch，其中inst_id = @ insid和PIid = @ piid和
 title = pd.title和order_Status = 2 
）， 0）
为0然后'完成'
e lse'未完成'结束为Order_stat 
 
来自
 dbo.Performa_details pd，
 Client_Goods_details_master po，
 Stock_Dispatch sd 
右外连接Performa_details pdio on pdio .PIid = sd.PIid，
 Performa_invoice pii 
 
其中po.PIid = pd.PIid和pd.PIid = pii.PI_id和Grnid = @ grnid和pii.inst_id=@insid 
结束

一旦我这样做，很容易发现子查询中的差异。

我也注意到你有多种方法可以连接表格。我的建议是保持一致 - 我个人更喜欢使用 ON 子句，因为它可以更容易地查看所使用的实际过滤器是什么，也可以发现任何错误。我尝试从
dbo.Performa_details pd
内部加入客户端_Goods_details_master po on po.PIid = pd.PIid
Stock_Dispatch sd，
右外连接performa_details pdio on pdio.PIid = sd.PIid，
内连接performa_invoice pii on pd.PIid = pii.PI_id
其中Grnid = @ grnid和pii.inst_id = @ insid

并且无法看到 Stock_Dispatch 如何链接到其他表（Performa_details除外）

ALTER procedure [dbo].[track_order1]
@grnid int,
@insid int,
@piid int
as
begin
Select distinct pii.inst_id, pd.PIid, pd.title,pd.publisher,pd.format ,pd.subscription_period_from as Sub_from,
pd.subscription_period_to as sub_to,pd.Qty_in_year AS Frequency
,(Select distinct(volumn) from  Stock_Dispatch  where inst_id=@insid and PIid=@piid and title= pd.title) As Volumn,Coalesce((Select Sum(cast(Dispatch_Qty  as int)) 
 from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
title= pd.title and order_Status=2) ,0)as Rec_qtys,
pd.Qty_in_year -Coalesce((Select Sum(cast(Dispatch_Qty  as int)) 
 from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
title= pd.title and order_Status=2) ,0) as rem,
Coalesce((Select Sum(cast(Dispatch_Qty  as int)) from 
dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=1),0) as New_dispatch,
 case pd.Qty_in_year -Coalesce((Select Sum(cast(Dispatch_Qty  as int)) 
 from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
title= pd.title and order_Status=2) ,0) when 0 then 'Complete'
else 'Not Completed' end as Order_stat
from  dbo.Performa_details pd, Client_Goods_details_master po
,Stock_Dispatch sd right outer join Performa_details pdio on pdio.PIid=sd.PIid , 
Performa_invoice pii where po.PIid=pd.PIid and pd.PIid=pii.PI_id 
and Grnid=@grnid and pii.inst_id=@insid
end

where is problem in this code i am not able to guess please give me suggestion thank's a allot frd

解决方案

Its clear from the message what's the issue. One (or more ) of your sub query is returning multiple rows where single row was expected. Its difficult for any boarder to tell the real cause unless we have access to your database. The possible to way to identify is comment all the sub queries and uncomment and see which one is causing a issue. Then you need to modify the query as per your business need such that it return single row. Good luck.

Further to Solution 1 ... where ArunRajendra is exactly right in describing the approach you should take to finding the problem...

Most of your sub-queries are returning sum of something ... except this one

(Select distinct(volumn) from  Stock_Dispatch  where inst_id=@insid and PIid=@piid and title= pd.title) As Volumn

... which I suspect is the cause of your problem.

Another way you can make life easier for yourself is to use whitespace ... spaces, tabs, newlines ... this is your unaltered code reformatted with some whitespace to make it more readable ...

ALTER procedure [dbo].[track_order1]
@grnid int,
@insid int,
@piid int
as
begin
	Select distinct pii.inst_id, pd.PIid, pd.title,pd.publisher,pd.format ,pd.subscription_period_from as Sub_from,	pd.subscription_period_to as sub_to,pd.Qty_in_year AS Frequency	,
	(
		Select distinct(volumn) 
		from  Stock_Dispatch  where inst_id=@insid and PIid=@piid and title= pd.title
	) As Volumn,
	Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid 
		and title= pd.title and order_Status=2
	) ,0)as Rec_qtys,
	pd.Qty_in_year - Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) 
		from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=2
	) ,0) as rem,
	
	Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) from 
		dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=1
	),0) as New_dispatch,
	case pd.Qty_in_year - Coalesce(
	(
		Select Sum(cast(Dispatch_Qty  as int)) 
		from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and 
		title= pd.title and order_Status=2
	) ,0) 
	when 0 then 'Complete'
	else 'Not Completed' end as Order_stat

	from  
		dbo.Performa_details pd, 
		Client_Goods_details_master po,
		Stock_Dispatch sd 
		right outer join Performa_details pdio on pdio.PIid=sd.PIid,
		Performa_invoice pii 

	where po.PIid=pd.PIid and pd.PIid=pii.PI_id and Grnid=@grnid and pii.inst_id=@insid
end

Once I had done that it was very easy to spot the difference in the sub-queries.
I also notice that you have a mix of methods of joining tables. My advice would be to be consistent - I personally prefer to use ON clauses as it makes it easier to see what the actual filter being used is and also to spot any errors. I had an attempt at your from clause

from
    dbo.Performa_details pd
    inner join Client_Goods_details_master po on po.PIid=pd.PIid
    Stock_Dispatch sd, 
    right outer join Performa_details pdio on pdio.PIid=sd.PIid,
    inner join Performa_invoice pii on pd.PIid=pii.PI_id
    where Grnid=@grnid and pii.inst_id=@insid

and couldn't see how Stock_Dispatch was linked to the other tables (other than Performa_details)

这篇关于消息512，级别16，状态1，过程track_order1，行8子查询返回多个值。子查询遵循=，！=，<，< =，>，> =或子查询用作...时不允许这样做的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！