Re: Compatible UNION query for postgresql and MS SQL Server

"Randy Zierman" <randy(dot)zierman(at)HelmSolutionsGroup(dot)com> writes:
> String strSQL =3D "Select m.* from Message m, ASKUser u " +
> "where (m.JurisdictionID =3D " + currentJurisdictionID + " AND
> DistributionType =3D 'J') " +
> "OR DistributionType =3D 'M' OR DistributionType =3D 'S' " +
> "AND (Status =3D 'O' " +
> "and u.ASKUserID =3D " + currentASKUserID + ") " +
> "Union " +
> "Select m.* from UserSchool us " +
> "inner join ASKUser u on u.ASKUserID =3D us.ASKUserID " +
> "inner join MessageSchool ms on ms.SchoolID =3D us.SchoolID " +
> "inner join Message m on m.MessageID =3D ms.MessageID " +
> "where us.ASKUserID =3D " + currentASKUserID +
> "ORDER BY m.Priority, m.DistributionType, m.CreationDate ";
> =20
> The problem is in the ORDER BY clause. If the table alias (m.) is not
> given, SQL server complains that there is an ambiguous column reference.

Go Microsoft :-( ... guess they can't read the SQL spec. The ORDER BY
really ought to see only the column names output by the UNION --- which
do not have any attached table alias.

It's barely possible that SQL Server is mis-parsing the construct and
thinks that the ORDER BY applies to the second SELECT, not the UNION
output as it should per-spec. You could try putting in parentheses
to see if that helps:

(SELECT ...
UNION
SELECT ...)
ORDER BY Priority, etc

If that doesn't work, the only other thing I can think of is a
sub-select:

SELECT * FROM
(SELECT ...
UNION
SELECT ...) ss
ORDER BY Priority, etc

I would not be surprised to hear that SQL Server can't handle either
of these (perfectly spec-compliant) syntaxes, in which case you're
probably stuck ...

regards, tom lane