CODEWITHSUNDEEP
androidsqlite
zohaib
zohaib

Reputation: 191

Greater than operator (>) not giving correct result from database

I've text type column named 'amountDesc' having some values in it. I want to get all values which have values greater than 100. I wrote a query but it's not giving the correct result.

Database as you can see as under. enter image description here

i've tried this code:

String query = "SELECT amountDesc FROM increment WHERE amountDesc > 100";

        Cursor rawQuery = getReadableDatabase().rawQuery(query, null);
        if (rawQuery.moveToFirst()) {
            while (!rawQuery.isAfterLast()) {
                String value = rawQuery.getString(rawQuery.getColumnIndex("amountDesc"));

                rawQuery.moveToNext();
                Log.d("tvlateamoutn1", value);
            }
        }

and getting these values on Logcat:

500 50 200 50

as you can see its not correct values as I required > 100 values. I know its question of for most beginners level but I stuck in it. Kindly resolve.

Upvotes: 1

Views: 577

Answers (2)

Prashant Kumar Mishra
Prashant Kumar Mishra

Reputation: 61

Can you check data type of amountDesc in schema. If declared data type is string, you can not compare with integer (100).

Upvotes: 0

Shawn
Shawn

Reputation: 52419

I've text type column named 'amountDesc' having some values in it.

So in your table definition you have amountDesc TEXT or something equivalent?

From the documentation:

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

and:

If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand.

Since the column has text affinity, the other operand is being converted from the integer 100 to the string '100'. The string '50' is greater than the string '100' because '5' is greater than '1'. Thus, your query is returning exactly what you're asking it to return. You're just asking it something different from what you think you are.

If you want to treat the values in that column as integers and compare them accordingly, use INTEGER not TEXT when creating the table. A poor workaround for not picking the correct affinity for the data stored in the column is to cast the values to the appropriate type when using them in calculations... CAST(amountDesc AS INTEGER) > 100 or something like that.

(Reading and understanding the linked documentation on datatypes and affinity is essential for using sqlite effectively.)

Upvotes: 2

Related Questions