SQL查询（子查询返回多个值..） [英] Sql query (subquery returned more than 1 value..)

问题描述

我有一个查询...

i have a query...

<pre>ALTER PROCEDURE [dbo].[SpWeighTicketReport]
@WeighTicketCode VARCHAR(50) = '',
@StartDate AS SMALLDATETIME = NULL,
@EndDate AS SMALLDATETIME = NULL,
@StrSearch AS VARCHAR(2000) = NULL
AS
SET NOCOUNT ON
DECLARE @StrQuery VARCHAR(8000) = ''
BEGIN
	SET @StrQuery = 'SELECT 
					 (SELECT CompanyName FROM CompanyMaster) AS CompanyName, WeighTicketCode, 
					 VehicleCode, CustomerName, ProductCode, ProductName, TransporterName, ContractNo, DONo, 
					 PL3No, TransactionNote, DriverName, ModifyBy AS WeighBy, WeighDate1, WeighDate2, 
					 Weigh1, R1, Weigh2, R2, FFA, Mouisture, Dirt, Broken,
					 (CASE WHEN R1 > Weigh1 THEN R1 - Weigh1 ELSE Weigh1 - R1 END) AS V1, 
					 (CASE WHEN R2 > Weigh2 THEN R2 - Weigh2 ELSE Weigh2 - R2 END) AS V2,
					 (CASE WHEN Weigh1 > Weigh2 THEN Weigh1 - Weigh2 ELSE Weigh2 - Weigh1 END) AS Netto, 
					 (CASE WHEN R1 > R2 THEN R1 - R2 ELSE R2 - R1 END) AS RNetto,
					 (CASE WHEN R1 > R2 THEN R1 - R2 ELSE R2 - R1 END) 
					 - 
					 (CASE WHEN Weigh1 > Weigh2 THEN Weigh1 - Weigh2 ELSE Weigh2 - Weigh1 END) AS VNetto,
					 AdjustWeigh, WeighNet, UOM1, Ext, Potongan, 
					 ' + QUOTENAME(CONVERT(VARCHAR, ISNULL(@StartDate, GETDATE()), 106) + ' s.d. ' + CONVERT(VARCHAR, ISNULL(@EndDate, GETDATE()), 106), '''') + ' AS Periode 
					 FROM 
					 VWeighTicket '			 
	IF LEN(@WeighTicketCode) > 0
		BEGIN
			SET @StrQuery = @StrQuery + ' WHERE WeighTicketCode = ' + QUOTENAME(@WeighTicketCode, '''') 
		END
	ELSE IF LEN(@StrSearch) > 0
		BEGIN
			SET @StrQuery = @StrQuery + ' ' + @StrSearch + ' AND CONVERT(VARCHAR, WeighDate1, 112) BETWEEN CONVERT(VARCHAR, CAST(' + QUOTENAME(@StartDate, '''') + ' AS DATE), 112) AND CONVERT(VARCHAR, CAST(' + QUOTENAME(@EndDate, '''') + ' AS DATE), 112) '
		END      
	ELSE
		BEGIN
			SET @StrQuery = @StrQuery + ' WHERE CONVERT(VARCHAR, WeighDate1, 112) BETWEEN CONVERT(VARCHAR, CAST(' + QUOTENAME(@StartDate, '''') + ' AS DATE), 112) AND CONVERT(VARCHAR, CAST(' + QUOTENAME(@EndDate, '''') + ' AS DATE), 112) '
		END
	SET	@StrQuery = @StrQuery + ' ORDER BY ID ASC'
END
EXEC (@StrQuery)

我收到错误...

and i get error...

Subquery returned more than 1 value...

请告知...... :(



我的尝试：



i change'='to'IN'



但仍有相同的错误...

please advise... :(

What I have tried:

i change '=' to 'IN'

but there is still the same errors...

推荐答案

好的。消息说明了一切。

在SELECT列表中你有 -

Ok. The message says it all.
In the SELECT list you have -

SELECT CompanyName FROM CompanyMaster

这可能会返回多于1的值。你可以这样做 -

This could return more than 1 value. You can do something like -

SELECT TOP 1 CompanyName FROM CompanyMaster

但是，问题是你可能看不到所有人想要的CompanyName，事实上你会看到一个公司名称。尝试编写相关的子查询，例如 -

But, problem is you may not see the desired CompanyName for all, in fact you will see one company name for all. Try writing a related subquery like-

SELECT (SELECT CompanyName FROM CompanyMaster WHERE IDCompany=VWeighTicket.CompanyID) AS CompanyName,... --other columns
FROM VWeighTicket
--rest of your query goes here

注意：这只是一个示例，您需要根据表/视图结构查找关系和列名。



尝试这样做并告诉我它是否有帮助：）

Note: This is just an example, you need to find the relation and column names according to your table/view structure.

Try doing this and let me know if it doesn't help :)

在下面的陈述中使用TOP 1

Use TOP 1 in below statement

(SELECT TOP 1 CompanyName FROM CompanyMaster) AS CompanyName

这篇关于SQL查询（子查询返回多个值..）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！