Link to home
Start Free TrialLog in
Avatar of metropia
metropiaFlag for United States of America

asked on

Subquery returned more than 1 value

I need help debugging my query.

I get error:

Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I do not know how to begin testing to see where the error come from exactly.

Please help.

SELECT DISTINCT
		sqcl.SalesQuoteNumber
	,	sqcl.ItemNumber 
	,	(
			SELECT DISTINCT
				s.SalesQuoteNumber 
			FROM 
				cost.vStd_SalesQuoteCostLine s  
			WHERE 
				s.CustomerNumber_BillTo = sqcl.CustomerNumber_BillTo
			AND 
				s.ItemNumber = sqcl.ItemNumber
			AND 
				s.DocumentDate = 
								(	SELECT MAX(s2.DocumentDate)
									FROM cost.vStd_SalesQuoteCostLine s2
									WHERE s2.CustomerNumber_BillTo = sqcl.CustomerNumber_BillTo
									AND s2.ItemNumber = sqcl.ItemNumber
									AND s2.DocumentDate < sqcl.DocumentDate
								)
			) AS PriorQuoteNumber 
FROM 
	cost.vStd_SalesQuoteCostLine sqcl
WHERE 
	sqcl.SalesQuoteNumber = @SalesQuoteNumber

Open in new window

SOLUTION
Avatar of Andrew Crofts
Andrew Crofts
Flag of Ukraine image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of metropia

ASKER

I tried to run it in isolation but it cannot find the identifier sqcl. how can i work around that issue?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial