SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)
This works if I use TOP 1
SELECT * FROM Table a
WHERE column1 IN (SELECT TOP 1 Column2 FROM Table2 b)
SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)
This works if I use TOP 1
SELECT * FROM Table a
WHERE column1 IN (SELECT TOP 1 Column2 FROM Table2 b)
Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= pr when the subquery is used as an expression.
Based on your error, are you 100% certain you are using "IN"? Can you try "= ANY" instead of "IN"
This works for me:
CREATE TABLE #t1 (a int NOT NULL)
INSERT #t1 (a) VALUES (1), (2), (3), (4)
CREATE TABLE #t2 (n int NOT NULL)
INSERT #t2 (n) VALUES (1), (2), (13), (14)
SELECT a.a FROM #t1 a WHERE a.a IN (SELECT b.n FROM #t2 b)
go
DROP TABLE #t1, #t2
I guess your actual query looks different, but it is difficult to troubleshoot a query we don't see.
DECLARE @sql nvarchar(max) = N'
(
SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)
)
EXEC (@sql)
It is hitting a linked server on db2
We still don't see the full statement.
If it is a linked server, it is possible that the OLE DB provider creates a non-working query on the linked server. One option is to run Profiler on the remote server to see what that might be,.
What does "SELECT @@version" return on the two servers?
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1)
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1)
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
I am running the query using Management Studio on my local machine.
One of the servers - I checked and the version returned is 2014.
If you don't answer the questions we ask you, we will not be able to help you.
We need to see the full query text. We need to see "SELECT @@version" from both servers.
And we ask for more questions after that.
You are not showing us exactly what you are doing. There is nothing wrong with the code you posted.
Please give us the actual code you are running.
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1')
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
insert into @itemnumbers values ('4')
insert into @itemnumbers values ('5')
insert into @itemnumbers values ('6')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1)
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1)
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1')
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
insert into @itemnumbers values ('4')
insert into @itemnumbers values ('5')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
Table1 a
WHERE column1 IN ''''' + (SELECT ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1)
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1)
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY
(
DB2System, N''
SELECT *
FROM
table1 a
WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''
''
)'
EXEC (@sql)
Hi @vj78-8539,
SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)
DECLARE @sql nvarchar(max) = (N'
SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)')
EXEC (@sql)
The two pieces of code you posted are correct, and after I tested it with my data, it returned the expected result.

When the subquery is after =, !=, <, <=, >, >=, or when the subquery is used as an expression, the subquery cannot return more than one value, otherwise this error will be returned.
We need you to provide a complete enquiry that you are conducting to know what problem you are experiencing.
Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
I do have to place some single quotes for this query to work. Below is the code
DECLARE @sql nvarchar(max) = N'
(
SELECT * FROM Table a
WHERE column1 IN ''''' + (SELECT TOP 1 Column2 FROM Table2 b) + '''''
''
)'
EXEC (@sql)
Why not like this?
DECLARE @sql nvarchar(max) = N'
SELECT * FROM Table a
WHERE column1 IN (SELECT TOP 1 Column2 FROM Table2 b)'
It seems that SELECT TOP 1 in your real query is executed separately during string concatenation. This will not always work without TOP 1.
Although, it is possible to build a comma-separated list of values and then build the dynamic query.
First of all, to insert code in a post, use the button with ones and zeroes on it.
It seems that you have not worked much with dynamic SQL. Dynamic SQL is an advanced feature, and in conjunction with OPENQUERY it is extra difficult. The key is to work with the pieces step by step, and first form the remote query, and then embed that into the dynamic SQL for OPENQUERY.
Here is something which may do what you want to do. It is based on a guess from the script you posted:
DECLARE @itemnumbers table
(
ID varchar(1000)
)
insert into @itemnumbers values ('1')
insert into @itemnumbers values ('2')
insert into @itemnumbers values ('3')
DECLARE @sq nchar(1) = N''''
DECLARE @list nvarchar(MAX)
SELECT @list =
(SELECT quotename(ID, @sq) + ', '
FROM @itemnumbers
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
SELECT @list = substring(@list, 1, len(@list) - 1)
DECLARE @innersql nvarchar(MAX) =
'SELECT *
FROM table1 a
WHERE column1 IN (' + @list + ')'
DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM OPENQUERY(DB2System, ' + @sq +
replace(@innersql, @sq, @sq + @sq) + @sq + ')'
PRINT @innersql
PRINT @sql
EXEC (@sql)
Note also the debug PRINTs.
12 people are following this question.