0

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?

prepbgg
  • 3,564
  • 10
  • 39
  • 51
  • Are thr fields that you want to order by in TableA or just in tableB – Conrad Frix Jan 02 '11 at 18:21
  • TableA and TableB both contain geographically related data items ... each table has an Easting and a Northing field (broadly equivalent to longitude and latitude). The sort order is the distance from a central point, so is a calculated field applied to every row in both tables by taking the Easting and Northing from each row. – prepbgg Jan 02 '11 at 22:36
  • Well, this is not related to your question. But, you better you StringBuilder instead of the string += string operations. Just a good practice (and huge performance gain if you are required to to these actions frequently in your app) – Sarwar Erfan Jan 04 '11 at 10:26

3 Answers3

2

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
Jaxidian
  • 13,081
  • 8
  • 83
  • 125
  • Thanks for this suggestion. Googling for "Android sqlite subquery" throws up a reference to an Android class called SQLiteQueryBuilder. I'll have a look at that. – prepbgg Jan 02 '11 at 22:41
  • This is the best solution, merge your two queries as one and then apply `order by` it worked! – Lalit Poptani Jun 19 '14 at 10:19
1

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.

prepbgg
  • 3,564
  • 10
  • 39
  • 51
0

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
Cesar A. Rivas
  • 1,355
  • 1
  • 10
  • 13
  • Thanks for these suggestions. I'd forgotten about "Between". Re custom functions, the discussion in this thread: http://stackoverflow.com/questions/2352320/sqlite-on-android-how-to-create-a-sqlite-dist-db-function-to-be-used-in-the-ap suggests that it is not at all easy to use custom sqlite functions in Android. – prepbgg Jan 02 '11 at 22:45