Query is causing an error. - SQL Server Forums

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)

Query is causing an error.

Author

Topic

mericlese
Starting Member

11 Posts

Posted - 2007-06-06 : 10:14:47

Here's my query:

SELECT AVG(TimeSpan) AS AverageUnassignedTime FROM ( SELECT *, DateDiff(n, OpenDate, Forwarded ) AS Timespan FROM (SELECT Calls.CallID, Calls.OpenDate, Calls.AnalystID, (SELECT '6/6/2007 8:47:28 AM' From Calls WHERE Calls.AnalystID = 0) AS Forwarded FROM Calls ) x WHERE x.Forwarded IS NOT NULL AND OpenDate >= '5/30/2007' AND OpenDate <= '6/6/2007 11:59:59 PM' and AnalystID = 0) y

And here's the error that is coming up:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can any one point out to me what's going wrong with this? Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 10:16:48

You have more than one record in your Calls table where AnalystID = 0.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mericlese
Starting Member

11 Posts

Posted - 2007-06-06 : 10:21:01

Why is that causing a problem? And how would I change it so that it would accept more than one record?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 10:29:02

It's giving problem because that's how RDBMS is designed to work. You can try using CASE statement in your case:

SELECT 
	AVG(TimeSpan) AS AverageUnassignedTime 
FROM 
	( 
	SELECT *, DateDiff(n, OpenDate, Forwarded ) AS Timespan 
	FROM 
		(
		SELECT 
			Calls.CallID, Calls.OpenDate, Calls.AnalystID, 
			Case when Calls.AnalystID = 0 then '6/6/2007 8:47:28 AM' end AS Forwarded 
		FROM Calls
		) x 
	WHERE 
		x.Forwarded IS NOT NULL 
		AND OpenDate >= '5/30/2007' 
		AND OpenDate <= '6/6/2007 11:59:59 PM' 
		and AnalystID = 0
	) y

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources