Subquery returned more than 1 value error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery returned more than 1 value error

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-11-23 : 13:29:06

I know I'm getting the below error because my subquery is returning more than one answer but I really need it to come back with a >= date for this full query to work. Can someone give me some help on how to get around this?

SELECT Traders.FirmName, SUM(Trades.TotalComm) AS Comm
FROM Traders INNER JOIN
Trades ON Traders.TraderID = Trades.Broker
WHERE (Trades.TradeDate >=
(SELECT FYE
FROM Traders AS Traders_1
WHERE (FYE IS NOT NULL))) AND (Trades.TradeDate < CONVERT(nvarchar(10), GETDATE(), 101)) AND (Traders.FYE IS NOT NULL)
GROUP BY Traders.FirmName

Thanks!
Phil

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 13:33:52

Use TOP 1?

If you need more help, please post sample data and expected result set, making sure to highlight to us which row you want included.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bobmcclellan
Starting Member

46 Posts

Posted - 2010-11-23 : 13:34:49

How about...
SELECT max(FYE)
FROM Traders AS Traders_1...

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-11-23 : 14:10:36

Sorry if I didn't make it clear.

Select MAX didn't work and Tara, please see below.

I highlighted the sub query green. The sub query returns 2 or more different dates like (10/31/2010 and 4/30/2010). The main query says to calculate a number for each firm from their FYE to yesterday so each firm will have their own FYE.

To give an example, below is what I would get if I DIDN'T use the sub query. I would get the result from each firm from 10/1/2010 to yesterday but this only should be the result for Raymond James because Morgan Stanely's FYE is for example 4/30/2010.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-11-26 : 11:45:53

Anyone? Please help.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 12:59:41

Based on your description, I think you wanted a list of all Firm names and FYE for each firm, and you need to join to that to limit results to the matching firm name with matching date criteria


;WITH tf AS
  ( Select FirmName, FYE
    FROM Traders
	Where FYE is not null 
  )

SELECT Traders.FirmName, SUM(Trades.TotalComm) AS Comm
FROM Traders INNER JOIN Trades 
	ON Traders.TraderID = Trades.Broker
		     INNER JOIN tf
	ON Traders.FirmName = tf.FirmName
WHERE (Trades.TradeDate >= tf.FYE
AND (Trades.TradeDate < CONVERT(nvarchar(10), GETDATE(), 101)) 
GROUP BY Traders.FirmName

Poor planning on your part does not constitute an emergency on my part.

Sachin.Nand

2937 Posts

Posted - 2010-11-26 : 13:48:09

quote:
Originally posted by pvong

SELECT Traders.FirmName, SUM(Trades.TotalComm) AS Comm
FROM Traders INNER JOIN
Trades ON Traders.TraderID = Trades.Broker
WHERE (Trades.TradeDate >=
(SELECT FYE
FROM Traders AS Traders_1
WHERE (FYE IS NOT NULL))) AND (Trades.TradeDate < CONVERT(nvarchar(10), GETDATE(), 101)) AND (Traders.FYE IS NOT NULL)
GROUP BY Traders.FirmName

I think you are overcomplicating your query.This should do your job



SELECT Traders.FirmName, SUM(Trades.TotalComm) AS Comm
FROM Traders INNER JOIN
Trades ON Traders.TraderID = Trades.Broker
WHERE (Trades.TradeDate >=FYE
AND Trades.TradeDate < CONVERT(nvarchar(10), GETDATE(), 101)) AND (Traders.FYE IS NOT NULL)
GROUP BY Traders.FirmName

PBUH

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-12-02 : 22:17:18

PBUH

This worked, thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources