Click here to Skip to main content
15,890,282 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a query...

SQL
<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...


please advise... :(

What I have tried:

i change '=' to 'IN'

but there is still the same errors...
Posted
Updated 22-May-17 0:15am

Ok. The message says it all.
In the SELECT list you have -
SQL
SELECT CompanyName FROM CompanyMaster

This could return more than 1 value. You can do something like -
SQL
SELECT TOP 1 CompanyName FROM CompanyMaster

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-
SQL
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 :)
 
Share this answer
 
Use TOP 1 in below statement
(SELECT TOP 1 CompanyName FROM CompanyMaster) AS CompanyName
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900