Thread: Compatible UNION query for postgresql and MS SQL Server

"Randy Zierman" <randy.zierman@HelmSolutionsGroup.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