Use joins within an OPENQUERY function

Joins are not supported within a single OPENQUERY statement. For example, the following query contains an implicit join between the Tag and Live tables, and will fail:

SELECT * FROM OPENQUERY(INSQL, 'SELECT v.DateTime, v.TagName, v.Value, t.Description

FROM Tag t, Live v

WHERE t.TagName LIKE "%Date%"

AND v.TagName = t.TagName

')

A workaround is to place the join outside of the OPENQUERY. For example:

SELECT v.DateTime, v.TagName, v.Value, t.Description

FROM OPENQUERY(INSQL, 'SELECT DateTime, TagName, Value

FROM Live

WHERE TagName LIKE "%Date%"

') v, Tag t

WHERE v.TagName = t.TagName

Explicit joins are also not supported within OPENQUERY. For example, the following query will fail:

SELECT * FROM OPENQUERY(INSQL, 'SELECT v.DateTime, v.TagName, v.Value, e.Unit

FROM Live v

JOIN AnalogTag t ON v.TagName = t.TagName

JOIN EngineeringUnit e ON t.EUKey = e.EUKey

WHERE v.TagName LIKE "%Date%"

')

A work-around is to place the join outside the OPENQUERY. For example:

SELECT v.DateTime, v.TagName, v.Value, e.Unit

FROM OPENQUERY(INSQL, 'SELECT DateTime, TagName, Value FROM Live

WHERE TagName LIKE "%Date%"

') v

JOIN AnalogTag t ON v.TagName = t.TagName

JOIN EngineeringUnit e ON t.EUKey = e.EUKey

ORDER BY t.TagName

In general, use four-part syntax wherever possible. All of the previous queries are more conveniently expressed in four-part syntax. For example, the syntax for the preceding query would be:

SELECT v.DateTime, v.TagName, v.Value, e.Unit

FROM INSQL.Runtime.dbo.History v

JOIN AnalogTag t ON v.TagName = t.TagName

JOIN EngineeringUnit e ON t.EUKey = e.EUKey

WHERE v.TagName LIKE '%Date%'

ORDER BY t.TagName