CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

How to use subquery in openquery where clause

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   |

Upvotes: 0

Views: 4286

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

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) +  ') ) '

Upvotes: 1

Related Questions