Solved

Subquery returned more than 1 value

Posted on 2014-02-11
6
287 Views
Last Modified: 2014-02-19
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

0
Comment
Question by:metropia
6 Comments
 
LVL 13

Assisted Solution

by:Philip Pinnell
Philip Pinnell earned 25 total points
ID: 39851426
I would assume that this

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

returns more than one record, which it isn't allowed to as it is providing just one field on the record of the main query.

run this in isolation and changed appropriately so it only returns one record, one field

top 1 maybe or a different where clause
0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 50 total points
ID: 39851438
You are getting more than one result from your second select distinct ...

Try running just this piece on its own, and see what you get for results. In order to use it within your main query, you want to have just one result.

			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
								)

Open in new window

0
 

Author Comment

by:metropia
ID: 39851445
I tried to run it in isolation but it cannot find the identifier sqcl. how can i work around that issue?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 50 total points
ID: 39851477
You just need to join the tables. I'm assuming an inner join here:

			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
									INNER JOIN cost.vStd_SalesQuoteCostLine sqcl
									ON s2.CustomerNumber_BillTo = sqcl.CustomerNumber_BillTo
									WHERE s2.ItemNumber = sqcl.ItemNumber
									AND s2.DocumentDate < sqcl.DocumentDate
								)

Open in new window

0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 300 total points
ID: 39851483
try the below query and see if you are getting more than one row for sales quote number

Then try to modify your original sub query  in order to get only one...

			SELECT DISTINCT
				s.SalesQuoteNumber 
			FROM 
				cost.vStd_SalesQuoteCostLine s,cost.vStd_SalesQuoteCostLine sQCL  
			WHERE 
				s.CustomerNumber_BillTo = sqcl.CustomerNumber_BillTo
			AND 
				s.ItemNumber = sqcl.ItemNumber
                        AND sqcl.SalesQuoteNumber = @SalesQuoteNumber
			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
								)

Open in new window


if you want to correct your query, then I think this will do

SELECT DISTINCT
		sqcl.SalesQuoteNumber
	,	sqcl.ItemNumber 
	,	(
			SELECT MAX(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

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 39851507
you can find the "itemnumber" by putting count(*) instead.
SELECT DISTINCT
		sqcl.SalesQuoteNumber
	,	sqcl.ItemNumber 
	,	(
			SELECT count(*)
			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


you may want to read my article for this kind of issues:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question