子查询返回的值超过1。当子查询跟随=，！=，> =或子查询用作表达式时，不允许这样做 [英] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression
本文介绍了子查询返回的值超过1。当子查询跟随=，！=，> =或子查询用作表达式时，不允许这样做的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用相同的查询语句没有问题，几天后我突然收到错误子查询返回的值超过1。当子查询跟随=，！=，<时不允许这样做< =，>，> =或当子查询用作表达式
如果有人可以帮我查一下查询是否有问题如下..
I have using the same query statement with no problem and a few day back suddenly I received an error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
appreciate if someone could help me to check if any problem with the query as below..
SELECT TOP (100) PERCENT YEAR(h.InvoiceDate) AS Year,
MONTH(h.InvoiceDate) AS Month,
h.CONumber,
CASE MONTH(InvoiceDate)
WHEN 1 THEN '1ST'
WHEN 2 THEN '1ST'
WHEN 3 THEN '1ST'
WHEN 4 THEN '2ND'
WHEN 5 THEN '2ND'
WHEN 6 THEN '2ND'
WHEN 7 THEN '3RD'
WHEN 8 THEN '3RD'
WHEN 9 THEN '3RD'
WHEN 10 THEN '4TH'
WHEN 11 THEN '4TH'
WHEN 12 THEN '4TH'
END AS Quarter,
i.ItemNumber,
i.ItemDescription,
CASE PRODUCT_GROUP
WHEN 00 THEN 'LAB'
WHEN 02 THEN 'PRO'
WHEN 10 THEN 'ANA'
END AS Division,
i.FamilySubgroup,
i.FamilyItemNumber,
c.CustomerName,
h.ShipToDeliveryLocationName,
CASE
WHEN CustomerCurrencyCode = '00000' THEN 'MYR'
ELSE CustomerCurrencyCode
END AS Currency,
ISNULL ((SELECT xl.InvoiceForeignUnitPrice
FROM dbo.FS_ARInvoiceHeader AS xh
LEFT OUTER JOIN dbo.FS_ARInvoiceLine AS xl
ON xh.ARInvoiceHeaderKey = xl.ARInvoiceHeaderKey
WHERE ( YEAR(xh.InvoiceDate) >= '2012' )
AND ( xh.InvoiceStatus <> 'X' )
AND ( xh.InvoiceStatus <> 'U' )
AND ( xl.LineItemNumber NOT LIKE 'PACK%' )
AND ( xl.LineItemNumber NOT LIKE 'FREIG%' )
AND ( xh.CustomerID NOT LIKE 'ZZDEMO%' )
AND ( i.ItemNumber IS NOT NULL )
AND ( xh.InvoiceType = 'R' )
AND ( xh.CONumber = h.CONumber )
AND ( xl.LineItemNumber = l.LineItemNumber )
AND ( xh.ShipmentNumberString = h.ShipmentNumberString )
AND ( xh.InvoiceDate > h.InvoiceDate )
GROUP BY xl.InvoiceForeignUnitPrice), 0) AS CreditUnitPrice,
ISNULL ((SELECT AVG(xl.ShipQuantity) AS Expr1
FROM dbo.FS_ARInvoiceHeader AS xh
LEFT OUTER JOIN dbo.FS_ARInvoiceLine AS xl
ON xh.ARInvoiceHeaderKey = xl.ARInvoiceHeaderKey
WHERE ( YEAR(xh.InvoiceDate) >= '2012' )
AND ( h.InvoiceStatus <> 'X' )
AND ( h.InvoiceStatus <> 'U' )
AND ( l.LineItemNumber NOT LIKE 'PACK%' )
AND ( l.LineItemNumber NOT LIKE 'FREIG%' )
AND ( c.CustomerID NOT LIKE 'ZZDEMO%' )
AND ( i.ItemNumber IS NOT NULL )
AND ( xh.InvoiceType = 'R' )
AND ( xh.CONumber = h.CONumber )
AND ( xl.LineItemNumber = l.LineItemNumber )
AND ( xh.ShipmentNumberString = h.ShipmentNumberString )
AND ( xh.InvoiceDate > h.InvoiceDate )), 0) AS ReturnQty,
CASE
WHEN InvoiceType = 'I' THEN InvoiceForeignUnitPrice
ELSE '0'
END AS InvoiceUnitPrice,
l.ShipQuantity,
i.MakeBuyCode,
CASE MakeBuyCode
WHEN 'B' THEN 'N'
WHEN 'S' THEN 'N'
WHEN 'M' THEN ( CASE CustomerClass4
WHEN '' THEN 'Y'
WHEN '2' THEN 'N'
WHEN '1' THEN ( CASE FamilySubgroup
WHEN '0205' THEN 'N'
WHEN '0016' THEN 'N'
WHEN '0017' THEN 'N'
WHEN '1021' THEN 'N'
WHEN '1022' THEN 'N'
ELSE 'Y'
END )
END )
END AS Lic
FROM dbo.FS_ARInvoiceHeader AS h
LEFT OUTER JOIN dbo.FS_ARInvoiceLine AS l
ON h.ARInvoiceHeaderKey = l.ARInvoiceHeaderKey
LEFT OUTER JOIN dbo.FS_Item AS i
ON l.ItemKey = i.ItemKey
LEFT OUTER JOIN dbo.FS_Customer AS c
ON h.CustomerID = c.CustomerID
LEFT OUTER JOIN dbo.zPRODUCT_CODE AS p
ON i.FamilySubgroup = p.PRODUCT_CODE
WHERE ( YEAR(h.InvoiceDate) >= '2012' )
AND ( h.InvoiceStatus <> 'X' )
AND ( h.InvoiceStatus <> 'U' )
AND ( l.LineItemNumber NOT LIKE 'PACK%' )
AND ( l.LineItemNumber NOT LIKE 'FREIG%' )
AND ( c.CustomerID NOT LIKE 'ZZDEMO%' )
AND ( i.ItemNumber IS NOT NULL )
AND ( h.InvoiceType = 'I' )
ORDER BY Month,
h.CONumber
推荐答案
经常被问到这个问题。请见 [ ^ ]。
在我看来，你的查询太长了。尝试以简单的方式编写它。检查每个子查询。您可以为子查询添加TOP（1）指令和SELECT语句。但我需要警告你：这不是完美的解决方案...
It is quite often asked question. Please, see[^].
In my opinion, your query is too long. Try to write it in simple manner. Check each subquery. You can addTOP(1)instruction together withSELECTstatement for subquery. But i need to warn you: it's not perfect solution...
Andrius Leonavicius写道，你必须检查以下选择的子选项：
ISNULL（（SELECT xl.InvoiceForeignUnitPrice
嗯，实际上这就是特定的问题。你需要使用Min（），Max来聚合它（ ），Avg（）或其他对您的业务逻辑最有意义的东西，同时删除GROUP BY xl.InvoiceForeignUnitPrice。
Andrius Leonavicius writes that you have to check the check the subselect starting with:
ISNULL ((SELECT xl.InvoiceForeignUnitPrice
Well, it's actually that specific line that is the problem. You need to aggregate it using Min(), Max(), Avg() or whatever is making best sense for your business logics, and at the same time remove the GROUP BY xl.InvoiceForeignUnitPrice.
这篇关于子查询返回的值超过1。当子查询跟随=，！=，> =或子查询用作表达式时，不允许这样做的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
