Click here to Skip to main content
15,890,123 members
Search within:


Sqlite listview results from query case when
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Android
SQLite
 
Trying to display the sqlite information in Android listview with dual sqlite condition.Query is tested in DB Browser which is giving desired results but i am unable bring the same in Android.


Current query
SQL
SELECT
    number as no,
    outletname as name,

    (CASE WHEN week = "WEEK1" THEN sunday ELSE 0 END) AS WK1S,
    (CASE WHEN week = "WEEK1" THEN monday  ELSE 0 END) AS WK1M,
    (CASE WHEN week = "WEEK1" THEN tuesday ELSE 0 END) AS WK1T,
    (CASE WHEN week = "WEEK1" THEN wednesday ELSE 0 END) AS WK1W,
    (CASE WHEN week = "WEEK1" THEN thursday ELSE 0 END) AS WK1T,
    (CASE WHEN week = "WEEK1" THEN saturday ELSE 0 END) AS WK1SA,
    (CASE WHEN week = "WEEK2" THEN sunday ELSE 0 END) AS WK2S,
    (CASE WHEN week = "WEEK2" THEN monday ELSE 0 END) AS WK21M,
    (CASE WHEN week = "WEEK3" THEN sunday ELSE 0 END) AS WK3S,
    (CASE WHEN week = "WEEK3" THEN monday ELSE 0 END) AS WK3M,
    (CASE WHEN week = "WEEK3" THEN tuesday ELSE 0 END) AS WK3T,
    (CASE WHEN week = "WEEK3" THEN wednesday ELSE 0 END) AS WK3W,
    (CASE WHEN week = "WEEK3" THEN thursday ELSE 0 END) AS WK3T,
    (CASE WHEN week = "WEEK3" THEN saturday ELSE 0 END) AS WK3SA

FROM labels5

     UNION   all
     SELECT "GRAND TOTAL",
    NULL ,


      COUNT(CASE WHEN week = "WEEK1" AND sunday LIKE "%sunday%" THEN 1 END) AS WK1S,
    COUNT(CASE WHEN week = "WEEK1" AND monday LIKE "%monday%" THEN 1 END) AS WK1M,
    COUNT(CASE WHEN week = "WEEK1" AND tuesday LIKE "%tuesday%" THEN 1 END) AS WK1T,
    COUNT(CASE WHEN week = "WEEK1" AND wednesday LIKE "%wednesday%" THEN 1 END) AS WK1W,
    COUNT(CASE WHEN week = "WEEK1" AND thursday LIKE "%thursday%" THEN 1 END) AS WK1T,
    COUNT(CASE WHEN week = "WEEK1" AND saturday LIKE "%saturday%" THEN 1 END) AS WK1SA,
    COUNT(CASE WHEN week = "WEEK2" AND sunday LIKE "%sunday%" THEN 1 END) AS WK2S,
   COUNT(CASE WHEN week = "WEEK2" AND monday LIKE "%monday%" THEN 1 END) AS WK2M,
      COUNT(CASE WHEN week = "WEEK1" AND sunday LIKE "%sunday%" THEN 1 END) AS WK3S,
    COUNT(CASE WHEN week = "WEEK3" AND monday LIKE "%monday%" THEN 1 END) AS WK3M,
    COUNT(CASE WHEN week = "WEEK3" AND tuesday LIKE "%tuesday%" THEN 1 END) AS WK3T,
    COUNT(CASE WHEN week = "WEEK3" AND wednesday LIKE "%wednesday%" THEN 1 END) AS WK3W,
    COUNT(CASE WHEN week = "WEEK3" AND thursday LIKE "%thursday%" THEN 1 END) AS WK3T,
    COUNT(CASE WHEN week = "WEEK3" AND saturday LIKE "%saturday%" THEN 1 END) AS WK3SA

     FROM labels5


What I have tried:

Java
SELECT
    number as no,
    outletname as name,

    (CASE WHEN week = "WEEK1" THEN sunday ELSE 0 END) AS WK1S,
    (CASE WHEN week = "WEEK1" THEN monday  ELSE 0 END) AS WK1M,
    (CASE WHEN week = "WEEK1" THEN tuesday ELSE 0 END) AS WK1T,
    (CASE WHEN week = "WEEK1" THEN wednesday ELSE 0 END) AS WK1W,
    (CASE WHEN week = "WEEK1" THEN thursday ELSE 0 END) AS WK1T,
    (CASE WHEN week = "WEEK1" THEN saturday ELSE 0 END) AS WK1SA,
    (CASE WHEN week = "WEEK2" THEN sunday ELSE 0 END) AS WK2S,
    (CASE WHEN week = "WEEK2" THEN monday ELSE 0 END) AS WK21M,
    (CASE WHEN week = "WEEK3" THEN sunday ELSE 0 END) AS WK3S,
    (CASE WHEN week = "WEEK3" THEN monday ELSE 0 END) AS WK3M,
    (CASE WHEN week = "WEEK3" THEN tuesday ELSE 0 END) AS WK3T,
    (CASE WHEN week = "WEEK3" THEN wednesday ELSE 0 END) AS WK3W,
    (CASE WHEN week = "WEEK3" THEN thursday ELSE 0 END) AS WK3T,
    (CASE WHEN week = "WEEK3" THEN saturday ELSE 0 END) AS WK3SA

FROM labels5

     UNION   all
     SELECT "GRAND TOTAL",
    NULL ,


      COUNT(CASE WHEN week = "WEEK1" AND sunday LIKE "%sunday%" THEN 1 END) AS WK1S,
    COUNT(CASE WHEN week = "WEEK1" AND monday LIKE "%monday%" THEN 1 END) AS WK1M,
    COUNT(CASE WHEN week = "WEEK1" AND tuesday LIKE "%tuesday%" THEN 1 END) AS WK1T,
    COUNT(CASE WHEN week = "WEEK1" AND wednesday LIKE "%wednesday%" THEN 1 END) AS WK1W,
    COUNT(CASE WHEN week = "WEEK1" AND thursday LIKE "%thursday%" THEN 1 END) AS WK1T,
    COUNT(CASE WHEN week = "WEEK1" AND saturday LIKE "%saturday%" THEN 1 END) AS WK1SA,
    COUNT(CASE WHEN week = "WEEK2" AND sunday LIKE "%sunday%" THEN 1 END) AS WK2S,
   COUNT(CASE WHEN week = "WEEK2" AND monday LIKE "%monday%" THEN 1 END) AS WK2M,
      COUNT(CASE WHEN week = "WEEK1" AND sunday LIKE "%sunday%" THEN 1 END) AS WK3S,
    COUNT(CASE WHEN week = "WEEK3" AND monday LIKE "%monday%" THEN 1 END) AS WK3M,
    COUNT(CASE WHEN week = "WEEK3" AND tuesday LIKE "%tuesday%" THEN 1 END) AS WK3T,
    COUNT(CASE WHEN week = "WEEK3" AND wednesday LIKE "%wednesday%" THEN 1 END) AS WK3W,
    COUNT(CASE WHEN week = "WEEK3" AND thursday LIKE "%thursday%" THEN 1 END) AS WK3T,
    COUNT(CASE WHEN week = "WEEK3" AND saturday LIKE "%saturday%" THEN 1 END) AS WK3SA

     FROM labels5

Very unsure about rawquery will support this scenario or not. Would appreciate if anyone can spot the light with suggest cursor query or any would much appreciated
Posted
Updated 30-Nov-19 5:05am
Add a Solution
Comments
Richard MacCutchan 30-Nov-19 4:56am    
You need to explain what results you do get in Android.
A.V2020 30-Nov-19 8:22am    
expected results. which is working fine in DBbrowser with above query and same thing needed in Android listview. image is uploaded in this

https://ibb.co/sWhQ63n
David Crow 30-Nov-19 10:36am    
"...but i am unable bring the same in Android."

Why not? What are you seeing (that appears wrong)?
A.V2020 30-Nov-19 11:01am    
@David Crow. than you for your feedback. I am unable to close the "Arrylist" my sqlquery. my question is given above query will work with db query or rawquery. in both i am unable to close the comments. infact i am not much familiar with array list. would be greate if you any guidance would be much appreciated
A.V2020 1-Dec-19 2:01am    
Richard & David any progress on my question :)?

1 solution

my arraylist is below
Java
<pre>    public ArrayList<HashMap<String, String>> getAllProducts12() {

        ArrayList<HashMap<String, String>> journalList;
        journalList = new ArrayList<HashMap<String, String>>();
        //String selectQuery = "SELECT  * FROM labels5";
        SQLiteDatabase database = this.getWritableDatabase();
       



        String selectQuery1 = "SELECT  number as no,outletname as name "  +

        ("CASE WHEN week = WEEK1 THEN sunday ELSE 0 END) AS WK1S" +
        ("CASE WHEN week = WEEK1 THEN monday  ELSE 0 END) + AS WK1M" +
        ("CASE WHEN week = WEEK1 THEN tuesday ELSE 0 END) + AS WK1T"+
        ("CASE WHEN week = WEEK1 THEN wednesday ELSE 0 END) + AS WK1W"+
        ("CASE WHEN week = WEEK1 THEN thursday ELSE 0 END) + AS WK1T" +
        ("CASE WHEN week = WEEK1 THEN saturday ELSE 0 END) + AS WK1SA"+
        ("CASE WHEN week = WEEK2 THEN sunday ELSE 0 END) + AS WK2S"+
        ("CASE WHEN week = WEEK2 THEN monday ELSE 0 END) + AS WK21M"+
        ("CASE WHEN week = WEEK3 THEN sunday ELSE 0 END) + AS WK3S"+
        ("CASE WHEN week = WEEK3 THEN monday ELSE 0 END) + AS WK3M"+
        ("CASE WHEN week = WEEK3 THEN tuesday ELSE 0 END) + AS WK3T"+
        ("CASE WHEN week = WEEK3 THEN wednesday ELSE 0 END) +AS WK3W"+
        ("CASE WHEN week = WEEK3 THEN thursday ELSE 0 END) + AS WK3T"+
        ("CASE WHEN week = WEEK3 THEN saturday ELSE 0 END) + AS WK3SA"+

                "FROM labels5 + UNION all + SELECT GRAND TOTAL" + "NULL" +


                "COUNT(CASE WHEN week = WEEK1 AND sunday LIKE %sunday% THEN 1 END) + AS WK1S" +
        "COUNT(CASE WHEN week = WEEK1 AND monday LIKE %monday% THEN 1 END) AS WK1M"+
        "COUNT(CASE WHEN week = WEEK1 AND tuesday LIKE %tuesday% THEN 1 END) AS WK1T"+
        "COUNT(CASE WHEN week = WEEK1 AND wednesday LIKE %wednesday% THEN 1 END) AS WK1W"+
        "COUNT(CASE WHEN week = WEEK1 AND thursday LIKE %thursday% THEN 1 END) AS WK1T"+
        "COUNT(CASE WHEN week = WEEK1 AND saturday LIKE %saturday% THEN 1 END) AS WK1SA"+
        "COUNT(CASE WHEN week = WEEK2 AND sunday LIKE %sunday% THEN 1 END) AS WK2S"+
        "COUNT(CASE WHEN week = WEEK2 AND monday LIKE %monday% THEN 1 END) AS WK2M"+
        "COUNT(CASE WHEN week = WEEK1 AND sunday LIKE %sunday% THEN 1 END) AS WK3S"+
        "COUNT(CASE WHEN week = WEEK3 AND monday LIKE %monday% THEN 1 END) AS WK3M"+
        "COUNT(CASE WHEN week = WEEK3 AND tuesday LIKE %tuesday% THEN 1 END) AS WK3T"+
        "COUNT(CASE WHEN week = WEEK3 AND wednesday LIKE %wednesday% THEN 1 END) AS WK3W"+
        "COUNT(CASE WHEN week = WEEK3 AND thursday LIKE %thursday% THEN 1 END) AS WK3T"+
        "COUNT(CASE WHEN week = WEEK3 AND saturday LIKE %saturday% THEN 1 END) AS WK3SA" +

        "FROM labels5" + "null");
        Cursor cursor = database.rawQuery(selectQuery1, null);
        if (cursor.moveToFirst()) {

            do {
                //Id, Company,Name,Price
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("id", cursor.getString(0));
                map.put("number", cursor.getString(1));
                map.put("outletname", cursor.getString(2));
                map.put("sunday", cursor.getString(3));
                map.put("monday", cursor.getString(4));
                map.put("tuesday", cursor.getString(5));
                map.put("wednesday", cursor.getString(6));
                map.put("thursday", cursor.getString(7));
                map.put("saturday", cursor.getString(8));
                map.put("closed", cursor.getString(9));
                map.put("calling", cursor.getString(10));
                map.put("week", cursor.getString(11));
                journalList.add(map);
                Log.e("dataofList",cursor.getString(0)+","+cursor.getString(1)+","+cursor.getString(2)+","+cursor.getString(3)+","+cursor.getString(4)+","+cursor.getString(4)+","+cursor.getString(6)+","+cursor.getString(7)+","+cursor.getString(8)+","+cursor.getString(9)+","+cursor.getString(10)+","+cursor.getString(11));
            } while (cursor.moveToNext());
        }



error in this area ? 
"FROM labels5" + "null");
 
Share this answer
 
Posted
Comments
Richard MacCutchan 30-Nov-19 13:41pm    
This is also not an answer or a problem description.
A.V2020 30-Nov-19 14:05pm    
Richard do you have any solution for my quesiton instead critizing my question ???
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Why I am getting wrong result from sqlite when using avg() function?
convert query in sqlite
Sqlite select much complicated in bash
Select Case Statement is not given the result to the following query
Display result sqlite query in textview
how to create a arabic query in sqlite
Listview search result onclick wrong return
Sqlite database lock
SQLite Database in C# application
Insert query in case statement

Advertise
Privacy
Cookies
Terms of Use
Last Updated 30 Nov 2019
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900