subquery result - 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)

subquery result

Author

Topic

kiri
Starting Member

38 Posts

Posted - 2008-02-13 : 15:17:49

select len(sub) from tution where subject='maths' and subno=0

select len(sub) from tution where subject='maths' and subno=1

it gives me
4
4
4
0
5
2
10

and
28
19
32

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

when i m using in my proc

declare @a int, @b int

set @a = (select len(sub) from tution where subject='maths' and subno=0)

set @b = (select len(sub) from tution where subject='maths' and subno=1)

SELECT distinct

subno,

sub = case when subno = 0 then sub
when subno= 1 then sub

from tution

can anybody help me where i have to use @a and @b to get correct results

talleyrand
Starting Member

35 Posts

Posted - 2008-02-13 : 18:10:41

When you run the first two queries as is, it retuns multiple rows of data. In your proc, you are attempting to stick multiple values into a pair of variables. A variable can only hold one value at a time so what value should @a hold? 4, 4, 4, 0, 5, 2, or 10?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 04:20:33

Do you want csv values?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 04:42:14

You need to specify what you want to do out of these values and whether you are interested in all value at a time. then we can suggest what you need to do. Can you please post your scenario?

kiri
Starting Member

38 Posts

Posted - 2008-02-14 : 14:14:43

quote:
Originally posted by madhivanan

Do you want csv values?

Madhivanan

Failing to plan is Planning to fail

yes, i want csv values..can u tell me how can i get that.

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 14:24:57

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

talleyrand
Starting Member

35 Posts

Posted - 2008-02-14 : 14:35:12

If you want CSV values, then you will also need to change out your data type from int to some other data type, perhaps a character.

declare @a int, @b int
^^^^
declare @a varchar(255), @b varchar(255)

kiri
Starting Member

38 Posts

Posted - 2008-02-14 : 14:53:06

thanks for ur reply..Peso and talleyrand

i change my datatype..but i can't understand how to use this in my query.

declare @a varchar(255), @b varchar(255)

set @a = (select len(sub) from tution where subject='maths' and subno=0)

set @b = (select len(sub) from tution where subject='maths' and subno=1)

SELECT distinct

subno,

sub = case when subno = 0 then sub
when subno= 1 then sub

from tution
-----------------------------------------------------------------
SELECT DISTINCT s1.subno,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.sub FROM @a AS s2 WHERE s2.subno = s1.subno ORDER BY ',' + s2.sub FOR XML PATH('')), 1, 1, '') AS CODES
FROM @a AS s1
ORDER BY s1.subno

it gives me error:
Msg 1087, Level 15, State 2, Line 5
Must declare the table variable "@a".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-15 : 02:13:11

Replace

FROM @a AS s1

to

FROM your_table AS s1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources