Using OPENQUERY with a table created on the fly

Question

I have a four-part linked server query that LEFT OUTER JOINs a table created on the fly using UNION ALL, something along these lines:

SELECT t.column1, rlt.s6, SUM(t.column3) column3
FROM Linked_server.database.schema.table t
    LEFT OUTER JOIN (
        SELECT 1 AS s7, 15 AS s6
        UNION ALL SELECT 3, 1
        UNION ALL SELECT 20, 16
    ) rlt ON (rlt.s7 = t.column2)

That works fine. However, when I'm running that same query with OPENQUERY function, like that:

SELECT * FROM OPENQUERY(Linked_server, '
    SELECT t.column1, rlt.s6, SUM(t.column3) column3
    FROM database.schema.table t
        LEFT OUTER JOIN (
            SELECT 1 AS s7, 15 AS s6
            UNION ALL SELECT 3, 1
            UNION ALL SELECT 20, 16
        ) rlt ON (rlt.s7 = t.column2)
')

, LEFT OUTER JOIN fails and s6 column only contains NULLs. I understand I could rewrite the query #2 to make it work, but I am curious why it doesn't work the way it is written? Is it because there is some limitation on OPENQUERY I am unaware of?

score 1 · Accepted Answer · answered Feb 26 '13 at 08:15

1

Haven't been able to reproduce this on my machine... The only difference is in the sort order of two queries. Maybe it just seems visually that result sets differ because of a different sort order. Try adding ORDER BY rlt.s6 DESC in both queries and then run them, this should give you result sets with same records arranged in same order.

answered Feb 26 '13 at 08:15

Ivan Golović

8,732
3
25
31

It turns out you're right and NULL values bubbled to the top simply because OPENQUERY implicitly applies a different sort order to the resultset. Thanks! – Wade Feb 27 '13 at 06:00

Using OPENQUERY with a table created on the fly

1 Answers1