June 22, 2006 at 7:23 am
June 22, 2006 at 7:50 am
What error do you get? "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
If table Test_table has more than one row, you will encounter problems as the error message tells you!
Rewrite as something like following code
select distinct top 10 so.*
from sysobjects so,
(
select fileformat
from [dbo].[Test_table]
) z
where so.name like '%' + z.fileformat + '%'
IF YOUR FILEFORMAT COLUMN HAS THE FULL NAME OF THE SYSOBJECT, please remove all the %'s for speed! And rewrite as follows
select distinct top 10 so.*
from sysobjects so
inner join (
select fileformat
from [dbo].[Test_table]
) z on so.name = z.fileformat
N 56°04'39.16"
E 12°55'05.25"
June 22, 2006 at 10:56 am
--equivalently,
select distinct top 10 so.*
from sysobjects so
inner join [dbo].[Test_table] t
on so.name = t.fileformat
select top 10 so.*
from sysobjects so
inner join (
select distinct fileformat
from [dbo].[Test_table]
) z on so.name = z.fileformat
--results are indeterministic because of the unordered TOP expression
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply