[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Subquery returned more than 1 value

Posted on 2014-02-11
6
Medium Priority
?
296 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 100 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 200 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 200 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 1200 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 500 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

656 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