question

vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended asked EchoLiu-msft commented

T-SQL IN Operator not working

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)



sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

0 Votes 0 ·
vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended answered RyanAbbey-0701 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Based on your error, are you 100% certain you are using "IN"? Can you try "= ANY" instead of "IN"

0 Votes 0 ·

Yes, I tried both = and IN but same error message.

0 Votes 0 ·

Not "=" but "= ANY" the "ANY" is important

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended answered vj78-8539 Suspended published

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

· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

0 Votes 0 ·

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)

0 Votes 0 ·

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)

0 Votes 0 ·

I am running the query using Management Studio on my local machine.
One of the servers - I checked and the version returned is 2014.

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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)

0 Votes 0 ·

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)

0 Votes 0 ·

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)

0 Votes 0 ·
vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended answered vj78-8539 Suspended published

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended answered vj78-8539 Suspended published

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended answered vj78-8539 Suspended published

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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.

119251-image.png

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.



image.png (17.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended answered Viorel-1 edited

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)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


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.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.