A.V2020 Ответов: 1

Результаты данных SQLite управления ListView с случае запроса при

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.


Текущий запрос
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


Что я уже пробовал:

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

Richard MacCutchan

Вам нужно объяснить, к каким результатам вы пришли. делать садись в Андроида.

A.V2020

предполагаемые результаты. который отлично работает в DBbrowser с вышеуказанным запросом и то же самое нужно в Android listview. изображение загружается в этот файл

https://ibb.co/sWhQ63n

David Crow

"...но я не могу принести то же самое в Android."

А почему бы и нет? Что вы видите (это кажется неправильным)?

A.V2020

@Дэвид Кроу. чем вы за ваши отзывы. Я не могу закрыть "Arrylist" моего sqlquery. мой вопрос дан выше запрос будет работать с db query или rawquery. в обоих случаях я не могу закрыть комментарии. на самом деле я не очень хорошо знаком со списком массивов. было бы здорово, если бы Вы были очень признательны за любое руководство

A.V2020

Ричард и Дэвид есть ли прогресс по моему вопросу :)?

1 Ответов

Рейтинг:
1

A.V2020

мой arraylist находится ниже

<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());
        }



ошибка в этой области ? 
"FROM labels5" + "null");

Richard MacCutchan

Это также не является ответом или описанием проблемы.

A.V2020

Ричард, у тебя есть какое-нибудь решение для моего вопроса вместо того, чтобы критиковать мой вопрос ???