I am adapting a java application to work with both SQL Server and
postgresql 7.2.2. I am having trouble with the following statement.
String strSQL = "Select m.* from Message m, ASKUser u " +
"where (m.JurisdictionID = " + currentJurisdictionID + " AND
DistributionType = 'J') " +
"OR DistributionType = 'M' OR DistributionType = 'S' " +
"AND (Status = 'O' " +
"and u.ASKUserID = " + currentASKUserID + ") " +
"Union " +
"Select m.* from UserSchool us " +
"inner join ASKUser u on u.ASKUserID = us.ASKUserID " +
"inner join MessageSchool ms on ms.SchoolID = us.SchoolID " +
"inner join Message m on m.MessageID = ms.MessageID " +
"where us.ASKUserID = " + currentASKUserID +
"ORDER BY m.Priority, m.DistributionType, m.CreationDate ";
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.
With postgres, using the table alias produces the error "Relation "m"
does not exist"
Is there any way, besides using column numbers, to use the same syntax
for both databases?