CODEWITHSUNDEEP
androidsqliteandroid-sqlite
comSaaxov
comSaaxov

Reputation: 73

SQLiteDatabase need help in query... please

I have this table:

id | CUPNAME | FRENCHNAME
1  | 2       | null
2  | null    | 4

I want to pull data from CUPNAME and FRENCHNAME which is not null. The table can be filled with numbers from 1 to 10. In this example, should be able to CUPNAME=2, FRENCHNAME = 4.

there is such a query:

SELECT sum(ifnull(french_press_bd.CUPNAME,0)) as a , sum(ifnull(french_press_bd.FRENCHNAME,0)) as b FROM my_table WHERE id in (1,2)

I try to write so it does not work:

 String sqr = "id in (1,2)";
Cursor cursor = sqdb.query(french_press_bd.TABLE_NAME, new String[] {                   
"sum(ifnull(french_press_bd.CUPNAME,0)) as a , sum(ifnull(french_press_bd.FRENCHNAME,0)) as b" 
},sqr, null,null,null,null );

Upvotes: 1

Views: 113

Answers (2)

CL.
CL.

Reputation: 180060

This query uses two scalar subqueries to get the first non-NULL value from the respective column:

SELECT (SELECT CUPNAME
        FROM my_table
        WHERE id IN (1, 2)
          AND CUPNAME IS NOT NULL
       ) AS CUPNAME,
       (SELECT FRENCHNAME
        FROM my_table
        WHERE id IN (1, 2)
          AND FRENCHNAME IS NOT NULL
       ) AS FRENCHNAME;

Since the outer query doesn't actually do anything, it might be simpler just to execute the two queries individually:

long cName = db.compileStatement("SELECT CUPNAME FROM my_table" +
                                 " WHERE id IN (1, 2) AND CUPNAME IS NOT NULL"
                                ).simpleQueryForLong();
long fName = db.compileStatement("SELECT FRENCHNAME FROM my_table" +
                                 " WHERE id IN (1, 2) AND FRENCHNAME IS NOT NULL"
                                ).simpleQueryForLong();

Upvotes: 0

Nizam
Nizam

Reputation: 5731

I think you can use rawQuery. As:

String your_query= "SELECT sum(ifnull(french_press_bd.CUPNAME,0)) as a , sum(ifnull(french_press_bd.FRENCHNAME,0)) as b FROM my_table WHERE id in (1,2)";
Cursor result = sqdb.rawQuery(your_query, null);

But make sure that SQLiteDatabase is not NULL. You have to call before it,

sqdb = dbhelper.getReadableDatabase(); // or getWritableDatabase()

Upvotes: 1

Related Questions