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?