Subquery returned more than 1 value

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

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-10-16 : 05:53:11

Often I come across the following error:

----------------------------------
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
----------------------------------

For e.g. the following generates the above error:

----------------------------------
DECLARE
@hours int,
@minutes int

SET @hours=(SELECT DATEDIFF(hh, SDateTime, EDateTime) FROM ETS)
SELECT @hours
----------------------------------

In general, how does one overcome this error?

Of course, instead of doing it like above, the following would give the resultset:

----------------------------------
SELECT DATEDIFF(hh, SDateTime, EDateTime) FROM ETS
----------------------------------

Please note that my question is just in general. The code snippets I have shown are just examples.

Thanks,

Ron

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-16 : 06:09:20

Try this

DECLARE @T TABLE (
hours int,
minutes int )
insert into @t ( hours)
SELECT DATEDIFF(hh, SDateTime, EDateTime) FROM ETS
SELECT hours FROM @T

If u have only one record in ETS TABLE then u can store value in a variable otherwise u have to declare a table...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-16 : 08:48:38

Not really sure what you are asking here. Either you want a single value or you want multiples. In your example you are assigning a select to a variable so it has to be a single value because a variable can only hold one value. Generally a sub query needs to be against a PK, some other unique value or an aggregate corrolated to the outer query to make any sense. Your example makes no sense unless you are trying to go down the table variable path.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources