printing value from select statement

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 2000 Forums

Transact-SQL (2000)

printing value from select statement

Author

Topic

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-29 : 02:52:01

Hi,

I have a select statement in which i have a sub query, which return the max value, but i getting the error the sub query cannot return more than one value.

my situation is somewhat similar to the following case

Declare TABLE sam
(
ID Int Identity,
code int,
val Int,
)

value in the table like
1,100,30
2,100,30
3,100,25
4,200,23
5,200,34
6,200,34

Declare TABLE code_tab
(
code int,
)

value in the table like
100,
200

the query is like this..

Select
(
Select MAX(value) Where code = code
)
from
code_tab

is it possible to from which record i am getting the error..,

is it possible to print some value like ID, from the select statement.so that we can able to identify which row has error.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:01:41

[code]
Select
(
Select MAX(value) FROM ???Where code = code
)
from
code_tab
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-29 : 03:05:47

This is the Exact Query, but its working fine... is it print any value in the select statement

Declare @sam TABLE
(
ID Int Identity,
code int,
val Int
)

Insert INTO @sam
Select 100,30 UNION ALL
Select 100,30 UNION ALL
Select 100,25 UNION ALL
Select 200,23 UNION ALL
Select 200,34 UNION ALL
Select 200,34

Declare @code_tab TABLE
(
code int
)

INSERT INTO @code_tab
Select 100
SELECT 200

Select
(
Select MAX(val) From @sam Where code = code
)
from
@code_tab

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:09:41

You missed the UNION ALL in the INSERT INTO @code_tab statement.

Sorry, I still don't quite get you. What is the problem here ? what are you trying to achieve ?

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-29 : 03:10:08

This is the what i am having... plz help me...

Declare @sam TABLE
(
ID Int Identity,
code int,
val Int
)

Insert INTO @sam
Select 100,30 UNION ALL
Select 100,30 UNION ALL
Select 100,25 UNION ALL
Select 200,23 UNION ALL
Select 200,34 UNION ALL
Select 200,34

Declare @code_tab TABLE
(
code int
)

INSERT INTO @code_tab
Select 100 UNION ALL
SELECT 200

Select
(
Select MAX(val) From @sam Where code = code
Group By Code
)
from
@code_tab

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-29 : 03:11:41

Ya you are right... the above post is the exact case.. what i have..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:16:43

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-29 : 03:18:24

i want to know from which row i getting this error...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:26:07

[code]SELECT code
FROM @sam
GROUP BY code
HAVING COUNT(*) > 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-29 : 03:31:07

this will give me no of rows having the similar values....

Thanks.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:38:04

This should be fine.

SELECT
(
	SELECT MAX(val) FROM @sam x WHERE x.Code = c.Code
	GROUP BY Code
)
FROM @Code_tab c

If you objective is to find out which records gives you more than one row in your sub query
basically you can use this select . . . group by .. having to determine the column value that gives you the problem.
or to see the full rows you can do something like this


select a.*
from   table a inner join (select col from table group by col having count(*) > 1) b on a.col = b.col

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources