Using a where sub select in a main open query select statement-CodePudding

I am trying to use a SQL server table in an open query against an Oracle database using a linked server.

My code is as follows.

select * 
INTO #customer
from openquery(
  [Linkedserver],
  'select 
    * 
  from
    customer c
    left join customer_contact cc ON c.main_contact_id = cc.contact_id
    where 
    c.customer_account in ('   (select customer_id from [database].[dbo]. 
    [ChangeCustomers])  ')

  '
   )

SELECT
 *
FROM 
    #customer

However the error I am receiving is

Msg 102, Level 15, State 1, Procedure customerchecker, Line 24 [Batch Start Line 7] Incorrect syntax near ' '. Msg 102, Level 15, State 1, Procedure customerchecker, Line 24 [Batch Start Line 7] Incorrect syntax near ' '.

Any help appreciated.

CodePudding user response：

You have two issues

You cannot use OPENQUERY with a variable or expression. It must be a literal, so you need dynamic SQL.
You need to aggregate the subquery, otherwise it will expect only one result.

DECLARE @sql nvarchar(max) = N'
select * 
INTO #customer
from openquery(
  [Linkedserver],
  ''select 
    * 
  from
    customer c
    left join customer_contact cc ON c.main_contact_id = cc.contact_id
    where 
    c.customer_account in (
        '   (
            select STRING_AGG(QUOTENAME(QUOTENAME(customer_id, ''''), ''''), ',')
            from [database].[dbo].[ChangeCustomers]
            )   '
    )
  ''
   );

SELECT
 *
FROM 
    #customer;
';

PRINT @sql; --for testing

EXEC sp_executesql @sql;

Note how the OPENQUERY part is double-escaped, because it's dynamic within dynamic.

I make no comment on the performance on such a query, nor why you decide to dump the data into a temp table only to immediately select it back out (you could have just had a normal SELECT).