Solved

Subquery returned more than 1 value

Posted on 2014-02-11
6
284 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

839 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