javaandroidsqliteandroid-listviewandroid-sqlite
SQlite listview results from query case when
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.
Real table looks like this:
Sqlite query
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
Query output:
LAYOUT:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="#FFC7C7C7"
android:orientation="vertical"
android:divider="@drawable/mydivider"
android:showDividers="middle"
android:dividerPadding="22dp"
android:weightSum="9">
<LinearLayout
android:id="@+id/lvcontainer"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_alignParentTop="true"
android:layout_weight="0.5"
android:background="#FFC7C7C7"
android:orientation="horizontal"
android:padding="1dp"
android:weightSum="3">
<TextView
android:id="@+id/txtproductcompany"
style="?android:dividerVertical"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="0.63"
android:gravity="left"
android:text="number"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductname"
style="?android:dividerVertical"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="0.59"
android:gravity="left"
android:padding="3dp"
android:text="name"
android:textColor="#000000"
android:textSize="9sp" />
<TextView
android:id="@+id/txtproductprice1"
style="?android:dividerVertical"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK1S"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice1"
style="?android:dividerVertical"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK1M"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK1T"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK1W"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice4"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK1TH"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice5"
android:layout_width="45dp"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2SA"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice122"
style="?android:dividerVertical"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2S"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice11"
style="?android:dividerVertical"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2M"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice22"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2T"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice31"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2W"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice41"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2TH"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice51"
android:layout_width="45dp"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK2SA"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice19"
style="?android:dividerVertical"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK3S"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice13"
style="?android:dividerVertical"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK3M"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice23"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK3T"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice33"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK3W"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice43"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK3TH"
android:textColor="#000000"
android:textSize="13sp" />
<TextView
android:id="@+id/txtproductprice53"
android:layout_width="45dp"
android:layout_height="wrap_content"
android:gravity="left"
android:padding="3dp"
android:text="WK3SA"
android:textColor="#000000"
android:textSize="13sp" />
</LinearLayout>
<ListView
android:id="@android:id/list"
android:layout_height="0dp"
android:layout_below="@+id/lvcontainer"
android:layout_weight="8.72"
android:layout_width="match_parent"
android:divider="@drawable/separator_line"
android:dividerHeight="4.0sp"
android:layout_marginTop="@dimen/activity_vertical_margin"
android:orientation="vertical"
android:dividerPadding="10dp"
android:layout_centerVertical="true"
android:showDividers="beginning|middle|end"
android:cacheColorHint="#00000000"
android:footerDividersEnabled="true"
android:headerDividersEnabled="true"
></ListView>
<TextView
android:id="@+id/txtresulttext"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_below="@android:id/list"
android:layout_marginLeft="5dp"
android:layout_marginTop="2dp"
android:layout_weight="0.5"
android:gravity="left"
android:text=""
android:textColor="#FFF55F54"
android:textSize="20sp"
android:textStyle="italic|bold"></TextView>
<LinearLayout
android:id="@+id/lvbottom"
android:layout_width="match_parent"
android:layout_height="0dp"
android:layout_alignParentBottom="true"
android:layout_weight="1"
android:orientation="horizontal"
android:weightSum="1">
<Button
android:id="@+id/btnupload"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1"
android:background="#1083f5"
android:gravity="center"
android:text="UPLOAD"
android:textColor="#ffffff"
android:textSize="15sp"
android:textStyle="bold"
android:visibility="invisible" />
</LinearLayout>
</LinearLayout>
tried with following cursor but no results
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 sql1="SELECT number,outletname ("+"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 thursday ELSE 0 END) AS WK1TH,"
+"(CASE WHEN week = WEEK1 THEN tuesday ELSE 0 END) AS WK1T,"
+"(CASE WHEN week = WEEK1 THEN wednesday ELSE 0 END) AS WK1W,"
+ "FROM" + "labels5 UNION all SELECT GRANDTOTAL" +"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 WK1MT," +
"COUNT(CASE WHEN week = WEEK1 AND thursday LIKE thursday THEN 1 END) AS WK1TH,"
+"COUNT(CASE WHEN week = WEEK1 AND wednesday LIKE wednesday THEN 1 END) AS WK1W,FROM" + "labels5";
Cursor cursor=database.rawQuery(sql1,null);
if (cursor.moveToFirst()) {
do {
//Id, Company,Name,Price
HashMap<String, String> map = new HashMap<String, String>();
map.put("WK1S", cursor.getString(0));
map.put("WK1M", cursor.getString(1));
map.put("WK1T", cursor.getString(2));
map.put("WK1W", cursor.getString(3));
map.put("WK1TH", cursor.getString(4));
journalList.add(map);
Log.e("dataofList",cursor.getString(0)+","+cursor.getString(1)+","+cursor.getString(2)+","+cursor.getString(3)+","+cursor.getString(4)+","+cursor.getString(4));
} while (cursor.moveToNext());
}
return journalList;error:
Caused by: android.database.sqlite.SQLiteException: no such function: GRANDTOTALNULLCOUNT (code 1 SQLITE_ERROR2): , while compiling: SELECT number,outletname (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 thursday ELSE 0 END) AS WK1TH,(CASE WHEN week = WEEK1 THEN tuesday ELSE 0 END) AS WK1T,(CASE WHEN week = WEEK1 THEN wednesday ELSE 0 END) AS WK1W,FROMlabels5 UNION all SELECT GRANDTOTALNULLCOUNT(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 WK1MT,COUNT(CASE WHEN week = WEEK1 AND thursday LIKE thursday THEN 1 END) AS WK1TH,COUNT(CASE WHEN week = WEEK1 AND wednesday LIKE wednesday THEN 1 END) AS WK1W,FROMlabels5 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
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
Solution
When you concatenate pieces of sql code, make sure to include spaces or commas between these pieces, and if a column like "GRAND TOTAL" contains illegal characters (a space) enclose it in square brackets, or if it is a string literal enclose it in single quotes:
String sql1 = "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";
- Parsing time strings like "1h 30min"
- Spring Boot control target JAR file name
- JavaFx sorting visualiser
- Does Java compiler understand two returns?
- Java: Compile inter-related classes
- What is the correct syntax for creating a HashMap in Java?
- Programmatically change log level in Log4j2
- Having trouble using a loop to find a user-input character from a text file in Java (Solved)
- How to return 404 response status in Spring Boot @ResponseBody - method return type is Response?
- How to use GWT 2.1 Data Presentation Widgets
- List of Java class file format major version numbers?
- UnsatisfiedDependencyException: Error creating bean with name
- Configure Selenium Nodes without a JSON file
- How can we extract values from URL when redirect to a Android app?
- GregorianCalendar returns wrong DAY_OF_WEEK in Java
- how to implement an idl-to-java compiler
- Prevent launching multiple instances of a java application by locking a file is not working
- Unable to execute Arquillian test
- ClassCastException in Production Build after Login in Android App
- Page through an array
- How to remove JRE entries from Windows registry?
- Chosing a suitable table size for a Hash
- How to set "value" to input web element using selenium?
- How to verify 'Open xdg-open popup' in Chrome via Selenium Webdriver?
- Java Generics: Who is right, javac or Eclipse compile?
- Where are the preferred argument conversions for overloaded methods documented (if at all)?
- How do I compile my Java source files?
- How to prevent Spring Boot/Hibernate from converting entity column names from PascalCase to snake_case?
- How add JAVA_OPTS variable to wildfly by kubernetes yaml file?
- Read a Environment Variable in Java with Websphere