Android SQLite "order by" calculated column in union query

I have been using the following code to query one table:

public Cursor fetchFilteredItemsNearSortedByDistance(String strTable, String[] strArrayFields, String strField, String strCriterion, double dblCentreEasting, double dblCentreNorthing, double dblRadius) {

  String strSQL = "Easting > " + Double.toString(dblCentreEasting-dblRadius);
  strSQL += " AND Easting < "  + Double.toString(dblCentreEasting+dblRadius);
  strSQL += " AND Northing > "  + Double.toString(dblCentreNorthing-dblRadius);
  strSQL += " AND Northing < "  + Double.toString(dblCentreNorthing+dblRadius);
  strSQL += " AND " + strField + " LIKE '%" + strCriterion + "%'";
  strSQL += " ORDER BY ";
  strSQL += "((Easting - " + Double.toString(dblCentreEasting)+")";
  strSQL += " * (Easting - " + Double.toString(dblCentreEasting)+")";
  strSQL += " + (Northing - " + Double.toString(dblCentreNorthing)+")";
  strSQL += " * (Northing - " + Double.toString(dblCentreNorthing)+"))";
  strSQL += " COLLATE NOCASE";

  return myDbHelper.myDataBase.query(strTable, strExtendedArrayFields, strSQL, null, null, null, null);     
}

The above code has been working satisfactorily. However, I now want to extend the idea to开发者_开发百科 a UNION of two or more tables, and have tried the following code:

public Cursor fetchFilteredPOIsNearSortedByDistance(String strTable, String[] strArrayFields, String strField, String strCriterion, double dblCentreEasting, double dblCentreNorthing, double dblRadius) {

  String strSQL = "SELECT * FROM TableA ";
  strSQL += "UNION SELECT * FROM TableB ";
  strSQL += "WHERE ( Easting > " + Double.toString(dblCentreEasting-dblRadius);
  strSQL += " AND Easting < "  + Double.toString(dblCentreEasting+dblRadius);
  strSQL += " AND Northing > "  + Double.toString(dblCentreNorthing-dblRadius);
  strSQL += " AND Northing < "  + Double.toString(dblCentreNorthing+dblRadius);
  strSQL += " AND " + strField + " LIKE '%" + strCriterion + "%' ) ";
  strSQL += " ORDER BY ";
  strSQL += "((Easting - " + Double.toString(dblCentreEasting)+")";
  strSQL += " * (Easting - " + Double.toString(dblCentreEasting)+")";
  strSQL += " + (Northing - " + Double.toString(dblCentreNorthing)+")";
  strSQL += " * (Northing - " + Double.toString(dblCentreNorthing)+"))";
  strSQL += " COLLATE NOCASE";

  return myDbHelper.myDataBase.rawQuery(strSQL, null);
}

However, this produces a "SQLiteException: 1st ORDER BY term does not match any column in the result set" error.

The full error in LogCat is:

ERROR/AndroidRuntime(11444): Caused by: android.database.sqlite.SQLiteException: 1st ORDER BY term does not match any column in the result set: , while compiling: SELECT * FROM TableA UNION SELECT * FROM TableB WHERE ( Easting > 594378.8427734375 AND Easting < 663822.8427734375 AND Northing > 127586.046875 AND Northing < 197030.046875 AND Name LIKE '%%' )  ORDER BY ((Easting - 629100.8427734375) * (Easting - 629100.8427734375) + (Northing - 162308.046875) * (Northing - 162308.046875)) COLLATE NOCASE

Can anyone help me onto the right track, please?

---

A complete guess (I'm not familiar with Android SQLLite but am other DB engines): If you put the result of your union into a subquery and then select * from the subquery and order those results, does this work? Something like this (with your columns/tables/etc.), if I were in SQL Server:

SELECT tblResults.* FROM
    (SELECT X FROM tblA
     UNION
     SELECT X FROM tblB) AS tblResults
ORDER BY tblResults.X

---

I think I must have made some mistakes in the syntax of my SQL.

In simplified terms, the code which did not work was:

SELECT * FROM TableA UNION SELECT * FROM TableB
WHERE (FieldA > X1 AND FieldA < X2 AND FieldB > Y1 AND FieldB < Y2
AND FieldC LIKE '%Criterion%')
ORDER BY [Function of FieldA and FieldB] COLLATE NOCASE

I have now replaced this with:

SELECT FieldA, FieldB, FieldC, .... ,
[Function of FieldA and FieldB] AS CalculatedField
FROM TableA
WHERE FieldA BETWEEN X1 AND X2
AND FieldB BETWEEN Y1 AND Y2
AND FieldC LIKE '%Criterion%'
UNION
SELECT FieldA, FieldB, FieldC, .... ,
[Function of FieldA and FieldB] AS CalculatedField
FROM TableB
WHERE FieldA BETWEEN X1 AND X2
AND FieldB BETWEEN Y1 AND Y2
AND FieldC LIKE '%Criterion%'
ORDER BY CalculatedField COLLATE NOCASE

Although this SQL statement is much longer, it seems to work, and what is more is amazingly quick ... the filtering on about 10,000 rows of data and sorting all happens in less than 50 milliseconds.

---

just some quick comments,

1. Instead of using > and <, try using the SQL statement Between
2. You should invest time in creating a custom SQLite function to return the items inside the radius, it will be better and with less change of messing up the query. Just google SQLite custom functions