1

I'm trying to perform a query in Android using: return query(selection, null, null, null), but cannot return the needed results when using multiple operators and parentheses. This is what I'm trying to do:

    selection = "(KEY_VARIABLE > '5' AND KEY_VARIABLE2 = 'Yes') OR (KEY_VARIABLE < '5' AND KEY_VARIABLE2 = 'No')"

However, the query returns 0 results, because the query does not recognize the parentheses.

Is there a way to form a query in Android using multiple operators with an operator embedded within an operator statement?

UPDATE: There's no exception in logcat. Everything works fine, except for the embedded OR(AND) statements. Here's the code:

 public Cursor getTierSchools() {

    String range = "";
    String range2 = "";
    String range3 = "";
    double score = LabValues.myscore;
    double scoreplustwo = score + 2;
    double scoreminustwo = score - 2;
    double scoreplusfive = score + 5;
    double scoreminusfive = score - 5;
    double scoreminusone = score - 1;

    range = "( " + KEY_SCHOOLSCORE + " > " + "'" + scoreplusfive + "'" + " AND " + KEY_SCHOOLSTATEBIAS + " = 'Yes' AND " + KEY_SCHOOLSTATELONG + " = " + "'" + LabValues.mystate + "'" + " )";
    range2 = " OR " + "( " + KEY_SCHOOLSCORE + " > " + "'" + scoreminusone + "'" + " AND " + KEY_SCHOOLSTATEBIAS + " = 'Yes' AND " + KEY_SCHOOLSTATELONG + " != " + "'" + LabValues.mystate + "'" + " )";
    range3 = " OR " + "( " + KEY_SCHOOLSCORE + " > " + "'" + scoreplustwo + "'" + " AND " + KEY_SCHOOLSTATEBIAS + " = 'No'" + " )";         

    String selection = range + range2 + range3;
    return query(selection, null, null, sorting);

}

private Cursor query(String selection, String[] selectionArgs, String[] columns, String sortOrder) {
    SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
    builder.setTables(FTS_VIRTUAL_TABLE);
    builder.setProjectionMap(mColumnMap);

    Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
            columns, selection, selectionArgs, null, null, sortOrder);

    if (cursor == null) {
        return null;
    } else if (!cursor.moveToFirst()) {
        cursor.close();
        return null;
    }
    return cursor;
}

Basically, I'm trying to integrate AND statements within parentheses, similar to how you would form an equation like: (4+4)/8 = 1 IS NOT EQUAL TO 4+4/8 = 4.5 .

SOLUTION:

    range = "(( " + KEY_SCHOOLSCORE + " > " + "'" + scoreplusfive + "'" + " AND " + KEY_SCHOOLSTATEBIAS + " = 'Yes' AND " + KEY_SCHOOLSTATELONG + " = " + "'" + LabValues.mystate + "'" + " )";
    range2 = " OR " + "( " + KEY_SCHOOLSCORE + " > " + "'" + scoreminusone + "'" + " AND " + KEY_SCHOOLSTATEBIAS + " = 'Yes' AND " + KEY_SCHOOLSTATELONG + " != " + "'" + LabValues.mystate + "'" + " )";
    range3 = " OR " + "( " + KEY_SCHOOLSCORE + " > " + "'" + scoreplustwo + "'" + " AND " + KEY_SCHOOLSTATEBIAS + " = 'No'" + " ))";
Shawn Gee
  • 551
  • 1
  • 4
  • 4
  • Please post the java code you are using to perform this query and the stacktrace of the exception from logcat if any. This isn't a selection, this is a where clause. Don't compare integers and strings (KEY_VARIABLE > '5' should be KEY_VARIABLE > 5). – smith324 Feb 09 '13 at 20:21
  • Thanks for your comment, please see the updated code. There's no exception/errors in logcat. – Shawn Gee Feb 09 '13 at 20:40
  • What does this code do? `return query(selection, null, null, sorting);` That is the interesting part, are you calling a SQLiteDatabase or ContentProvider? Your `selection` is not a selection, it is a where clause. Please show the complete sql statement in the form "select [columns] from [table] where [conditions]" – smith324 Feb 09 '13 at 20:59
  • Have you tried escaping the parenthesis? – Macarse Feb 09 '13 at 21:01
  • I'm calling an SQLiteDatabase I've tried without the parenthesis but obviously the AND and OR statements would be continuous and not function properly. The code is functioning as if the parenthesis are not there. – Shawn Gee Feb 09 '13 at 23:30
  • Code has been updated. Like I said, the code functions, but not properly, I can't figure out how to make a where clause like: ( a AND b) OR (c AND not b), similar to above. – Shawn Gee Feb 10 '13 at 00:15
  • Figured out the problem, pretty simple solution, was missing a set of parentheses on the range-range3 strings. Solution is above. – Shawn Gee Feb 10 '13 at 01:15

0 Answers0