运行宏时出错 - 子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。 [英] Error on running macro- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

问题描述

当我运行此代码时，它给了我这个错误，





子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。





代码如下，请尽快解决我的问题。

Hi,
When i am running this code ,it is giving me this error,


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


the code is like below,please resolve my problem ASAP.

Select ttt.FdDmCd,ttt.Col,ttt.CFP,sum(ttt.Qty)as FgQty,sum(ttt.gwt) as GldWt,sum(ttt.swt)as SilWt From
(Select  Fdbyy,fdbchr,fdbno,(select OdDmCol from OrdDsg Where Odtc=FdPrdOdtc and Odyy=FdPrdOdyy and odchr=FdPrdOdChr and
Odno=FdPrdOdNo and odsr=FdPrdOdSr)as col, (Select OlScd FRom OrdLab where OlMcd='CFP' and  Oltc=FdPrdOdtc and
 Olyy=FdPrdOdyy and olchr=FdPrdOdChr and Olno=FdPrdOdNo and olSr=FdPrdOdSr)as CFP, Max(FdDmCd)as FdDmCd,max(FdQty) as Qty,
 (Select Sum(case when frrmctg ='G' and frrmsctg='-' then  FrRmWt else 0 end) from fgrm Where FrFdIdNo =Max(FDIdNo)
Group by FrFdIdNo)as GWt, (Select Sum(case when frrmctg ='S' and frrmsctg='-'  then  FrRmWt else 0 end) from fgrm
Where FrFdIdNo =Max(FDIdNo) Group by FrFdIdNo)as SWt from fgd Join (Select Distinct fdbyy as fdbyy1,fdbchr as fdbchr1 ,
fdbno as fdbno1,Count(*)as Count1 from fgd Where fddt between '01/04/14' and '02/04/14' and fdtc='FB' and fdsubloc = 'exp'
 and FdPrdOdChr in ('JKS','KPS','JOB')
 Group by fdbyy,fdbchr,fdbno ) as tab on fdbyy=tab.fdbyy1 and fdbchr=tab.fdbchr1 and fdbno=tab.fdbno1
Where fddt between '01/04/14' and '02/04/14' and fdtc='FB'  and FdPrdOdChr in ('JKS','KPS','JOB')
group by Fdbyy,fdbchr,fdbno ,FdPrdOdtc ,FdPrdOdyy,FdPrdOdChr,FdPrdOdNo,FdPrdOdSr
Union all
Select  Fdbyy,fdbchr,fdbno,(select OdDmCol from OrdDsg Where Odtc=FdPrdOdtc and Odyy=FdPrdOdyy and odchr=FdPrdOdChr and
 Odno=FdPrdOdNo and odsr=FdPrdOdSr)as col, (Select OlScd FRom OrdLab where OlMcd='CFP' and  Oltc=FdPrdOdtc and
Olyy=FdPrdOdyy and olchr=FdPrdOdChr and Olno=FdPrdOdNo and olSr=FdPrdOdSr)as CFP, FdDmCd,FdQty,
(Select Sum(case when frrmctg ='G' and frrmsctg='-'  then  FrRmWt else 0 end) from fgrm Where FrFdIdNo =FDIdNo
Group by FrFdIdNo)as GWt, (Select Sum(case when frrmctg ='S' and frrmsctg='-'  then  FrRmWt else 0 end) from fgrm
Where FrFdIdNo =FDIdNo Group by FrFdIdNo)as SWt  from fgd  Join (Select Distinct fdbyy as fdbyy1,fdbchr as fdbchr1 ,
fdbno as fdbno1 from fgd Where fddt between '01/04/14' and '02/04/14'
and fdtc='FB'  and FdPrdOdChr in ('JKS','KPS','JOB') group by Fdbyy,fdbchr,fdbno ,FdPrdOdtc ,FdPrdOdyy,FdPrdOdChr,
FdPrdOdNo,FdPrdOdSr ) as tab on fdbyy=tab.fdbyy1 and fdbchr=tab.fdbchr1 and fdbno=tab.fdbno1
 Where fddt between '01/04/14' and '02/04/14' and fdtc='FB'  and fdsubloc = 'exp' and
 FdPrdOdChr in ('JKS','KPS','JOB') ) as ttt Group by ttt.FdDmCd,ttt.Col,ttt.CFP

推荐答案

错误消息说明究竟出了什么问题！来自子查询的数据与主查询无关，并返回多于1条记录！



使用太多子查询生成列可能是应用程序崩溃或性能问题的原因至少。



重新设计你的查询！从基础开始：检查每个子查询返回的结果！使用加入！小心使用子查询！



按照以下说明操作：标量子查询 [ ^ ]

The error meassage says exactly what is wrong! The data from subquery are not related with main query and return more then 1 record!

Using too many subqueries to produce columns could be the reason of application crash or performance issue at least.

Redesign your query! Start with basics: check what result returns each subquery! Use Joins! Be careful using subqueries!

Follow this instruction: Scalar Subqueries[^]

您使用了许多子查询。

检查每一项以确保它们不会返回多个查询。

You have used many subqueries.
Check each of these to ensure they don't return more than one query.

这篇关于运行宏时出错 - 子查询返回的值超过1。当子查询遵循=，！=，&lt;，&lt; =，&gt;，&gt; =或子查询用作表达式时，不允许这样做。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！