Solved

Subquery returned more than 1 value

Posted on 2014-02-11
6
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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