CODEWITHSUNDEEP
androidsqlite
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

SQlite Query Sorting by Wrong Column

Here is my table setup:

public static final String TABLE_DEBT = "debt";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_DEBT_NAME = "debt_name";
public static final String COLUMN_DEBT_TOTAL = "debt_total";
public static final String COLUMN_APR = "apr";
public static final String COLUMN_PAYMENT = "payment";
public static final String COLUMN_SPECIAL_RATE = "s_rate";
public static final String COLUMN_SPECIAL_TIME = "s_time";
public static final String COLUMN_FIRST_FEE = "first_apr_fee";
public static final String COLUMN_PAY_DATE = "pay_date";
private static final String DATABASE_NAME = "debt.db";
private static final int DATABASE_VERSION = 1;


private static final String DATABASE_CREATE = "create table " + TABLE_DEBT + "( " + COLUMN_ID + " integer primary key autoincrement, " + COLUMN_DEBT_NAME + " text not null, " + COLUMN_DEBT_TOTAL
            + " text not null, " + COLUMN_APR + " text not null, " + COLUMN_PAYMENT + " text not null, " + COLUMN_SPECIAL_RATE + " text, " + COLUMN_SPECIAL_TIME + " text, " + COLUMN_FIRST_FEE + " text, " + COLUMN_PAY_DATE + " text)";

I am using thse queries in this conditional format to sort by user selection:

    if (DebtPlanner.PlanType.equals("highint")) {
         c = database.rawQuery("SELECT *  FROM debt ORDER BY apr DESC;", null);
         Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);
    } else if (DebtPlanner.PlanType.equals("lowbal")) {
         c = database.rawQuery("SELECT *  FROM debt ORDER BY debt_total ASC;", null);
         Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);
    } else if (DebtPlanner.PlanType.equals("highbal")) {
         c = database.rawQuery("SELECT *  FROM debt ORDER BY debt_total DESC;", null);
         Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);
    }

Added Code:

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
            indName[j++] = c.getString(c.getColumnIndex("debt_name"));
            indBal[k++] = c.getDouble(c.getColumnIndex("debt_total"));
            indPay[l++] = c.getDouble(c.getColumnIndex("payment"));
            indApr[m++] = c.getDouble(c.getColumnIndex("apr"));
            indSRate[e++] = c.getString(c.getColumnIndex("s_rate"));
            indSTime[f++] = c.getInt(c.getColumnIndex("s_time"));
            indDay[d++] = c.getString(c.getColumnIndex("pay_date"));

            rowCounter[n++] = n;
        }


    for (int i : rowCounter) { 

       nameList.add(indName[r++]); // This is the name of each debt that shows out of order.
       // lots of edited calucaltion here

     }

However, they sort, but not by the expected values. For example, when I say to sort by debt_toal (either ASC or DESC), it sorts by debt_name (it gets the ASC or DESC part correct).

I looked at the actual table in the phone and all the data is in the correct columns. I am not even sure where to go from here?

Extra code:

public class DebtPlanner extends Application {

    public static String PlanType = "highint";


    public static String highIntPlan() {
        return DebtPlanner.PlanType = "highint";
    }

    public static String lowBalPlan() {
        return DebtPlanner.PlanType = "lowbal";
    }

    public static String highBalPlan() {
        return DebtPlanner.PlanType = "highbal";
    }

}

Upvotes: 1

Views: 381

Answers (2)

Sam
Sam

Reputation: 86948

When comparing String in Java you must use equals(), not ==:

if (DebtPlanner.PlanType.equals("default")) {

There are great explanations about this in How do I compare strings in Java?

Also you should use else if on your subsequent queries because they are mutually exclusive thoughts.

It has been sorting correctly all along. The problem is, the column is String. So, technically, 2 is larger than 1000000. Now I just need to figure out how to correct this...

Ok, use CAST to sort the column as an Integer rather than Text:

SELECT * FROM debt ORDER BY CAST(apr AS INTEGER) DESC;

Upvotes: 1

Danil Onyanov
Danil Onyanov

Reputation: 210

Use 

Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);

to ensure that you have correct data in your multiple ifs

Upvotes: 1

Related Questions