BUG: UNION Operator w/ ORDER BY Clause Causes Error 104

• Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

Under certain circumstances, when a SELECT statement with UNION operators has an ORDER BY clause, it gives error 104: "Order By items must appear in the select list if the statement has set operators."

A SELECT statement with UNION operators can have an ORDER BY clause in it as long as the clause appears once at the end of the last SELECT statement. Furthermore, it is a Transact-SQL extension that the columns appearing in the ORDER BY list need not appear in the select list. But this extension does not hold when the SELECT statement has set operators, such as UNION.

However, if the SELECT statement is of the form

     select convert(char, col1)
     from table1
     where <search condition>
     UNION
     select convert(char, col1)
     from table1
     where <search condition>
     order by col1

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. We are researching this problem and will post new information here as it becomes available.