How to use subquery in openquery where clause

Question

So I have this Openquery in a stored procedure, where I need to return results where the values in a column are the same as the ones in a local table

exec spx_SELECT_LocalizacoesEtiquetas 


GO
IF OBJECT_ID('dbo.spx_SELECT_LocalizacoesEtiquetas') IS NOT NULL
DROP PROCEDURE spx_SELECT_LocalizacoesEtiquetas
GO
CREATE PROCEDURE spx_SELECT_LocalizacoesEtiquetas   

AS
BEGIN
SET NOCOUNT ON 

DECLARE @SQL NVARCHAR(MAX); 
    SET @SQL = 

'SELECT ET0109 AS Localizacao, Etiquetas
FROM OpenQuery(MACPAC, ''SELECT FET001.ET0109,  FET001.ET0101 AS Etiquetas
FROM AUTO.D805DATPOR.FET001 FET001
WHERE FET001.ET0104=''''POE'''' AND FET001.ET0105=''''DIS'''''' AND FET001.ET0101 = ''''' 
+ (SELECT Localizacao FROM xLocalizacao WHERE InventarioID = 1 ) +  ''''' ) ';

EXEC sp_executesql @SQL

END

basically it won't accept the subquery 'cause it says it has too many values.... So my question is. How can i limit the values from the subquery where the values of a column match the ones in a local table? basically a where column A in open query = column B in local table

EDIT.

Here is what I'm trying to achieve.

SubQuery returns from Local table


 Column A
|   A   |
|   B   |
|   C   |
|   D   |
|   E   |

Open query returns

   Column A  Column B
  |   A   |    0   |
  |   A   |    0   |
  |   A1  |    1   |
  |   A   |    2   |
  |   B   |    3   |
  |   B   |    3   |
  |   B1  |    4   |


Final result should Be


    Final query
   Column A    Column B
  |   A   |    0   |
  |   A   |    0   |
  |   A   |    2   |
  |   B   |    3   |
  |   B   |    3   |

yes, i need the values from the column in the openquery to be the same as the subquery, im not sure if there is a better way to to this. Subquery returns x values from a column, i need to get a column from openquery where they match the subquery — Jackal, Feb 01 '19 at 15:44
A single column can only contain one value per row, so what are you hoping to achieve by basically saying `WHERE ColumnX = {more than one value}` If ColumnX = "a", it can't also = "b". I think you might need to explain this with an example of data, and desired results. It's not currently clear what you're trying to do. — Tab Alleman, Feb 01 '19 at 15:49
please see updated post, I show what i really intended to do — Jackal, Feb 01 '19 at 15:59

score 1 · Accepted Answer · answered Feb 01 '19 at 16:15

Ok, there are two changes you need to make in your approach.

First of all, you are concatenating your sub-query to a string. No matter what, your subquery has to return a single value, not a multi-row set. So you need to use the method of your choice for having your query return a comma-separated string.

Here's one that will work on any version of SQL Server after 2005.

in other words, instead of this:

 Column A
|   A   |
|   B   |
|   C   |
|   D   |
|   E   |

your subquery needs to return a single varchar column containing this:

'A','B','C','D','E'

The next change you need to make is using IN instead of =.

So instead of this:

AND FET001.ET0101 = ''''' 
+ (Your Subquery) +  ''''' ) '

you need this:

AND FET001.ET0101 IN ( ' 
+ (Your Subquery) +  ') ) '

so I need to merge the rows from my subquery into 1 but does it return all rows with values separated from other openquery? — Jackal, Feb 01 '19 at 16:18

How to use subquery in openquery where clause

1 Answers1