Android Question SQLite Query help

[walterf25]

Hello guys, i'm in need of some help from anyone who has experience with SQLite, I need to create a query that will pull data from 4 different tables, this is an app i'am working on for a client of mine, and unfortunately i don't have the necessary experience to deal with this kind of function in SQLite.

I'am Using the UltimateListview library for this app, the listview needs to load over 10000 items, which from what i understand this ultimatelistview should load this items very quick, the issue i'm having is that the query takes too darn long to execute.

the table name that i need to fill is Price_List_Items, this tables consists of 7 columns.

[Item Code] [Category] [Description] [Price] [Availability] [Order] [Favorite]

the data from the first 4 columns is retrieved from one table called items, the data from the 5th columns needs to be pulled from another table called onhand, the sixth column is actually a value that needs to be entered therefore there needs to be an update query to insert that value into the record and the 7th and last column is a checkbox on the listview that marks any specific row as a favorite item, there's another table called favorites which stores a 1 or a 0 depending on whether the user decides to mark that record as a favorite.

what i need is a query that will pull all the information from the items table which will fill the first 4 columns and that will also pull the information from the onhand table and that will fill the 5th column and that will also pull the information from the order table and from the favorite table.

Can anyone please help me with this or at least point me in the right direction. so far i'm using the query below that a friend of mine made for me, but as i mentioned at the beginning of this post the query takes too long to execute therefore slowing down the process of filling the listview with the +10000 items.

Sub Price_list_General(customer As String)
Dim cadena As String =""
Dim otrac As String=""
otrac=cadenaR(customer)
cadena = "Select distinct(c.account) As customer,(Select id_break from price_break pp where o.id_break=pp.id_break) As id_break,(Select quantity from price_break pp where o.id_break=pp.id_break) As quantity,i.category,i.itemcode As ITEMCODE,I.description As DESCRIPTION, I.pricing As PRICING,(Select price from price_break pp where o.id_break=pp.id_break) As price_break,(Select ONHAND from availability AA where  o.items_itemcode=AA.itemcode)As Availability,o.cantidad As ORDEN, ' ' AS FAVORITO" _
&"  FROM CUSTOMERS C" _
&"  INNER JOIN  ORDEN O" _
&"  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
&"  INNER JOIN ITEMS I " _
&"  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  LEFT JOIN AVAILABILITY A" _
&"  ON I.ITEMCODE=A.itemcode" _
&"  LEFT JOIN PRICE_BREAK pb" _
&"  ON I.ITEMCODE=PB.ITEMCODE" _
&"  WHERE  customer="& customer _
&"  AND I.ITEMCODE NOT IN (" _
&"  select DISTINCT(I.ITEMCODE) " _
&"  FROM CUSTOMERS C" _
&"  INNER JOIN  ORDEN O" _
&"  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
&"  INNER JOIN ITEMS I " _
&"  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  INNER JOIN FAVORITO F " _
&"  ON c.account=f.customers_account" _
&"  AND F.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  LEFT JOIN AVAILABILITY A" _
&"  ON I.ITEMCODE=A.itemcode" _
&"  LEFT JOIN PRICE_BREAK pb" _
&"  ON I.ITEMCODE=PB.ITEMCODE" _
&"  WHERE  C.account=" & customer _
&"  )" _
&"  UNION ALL" _
&"  select distinct(c.account) as customer,(Select id_break from price_break pp where f.id_break=pp.id_break) As id_break,(Select quantity from price_break pp where f.id_break=pp.id_break) As quantity,i.category,i.itemcode as ITEMCODE,I.description AS DESCRIPTION, I.pricing AS PRICING,(select price from price_break pp where F.id_break=pp.id_break) as price_break,(select ONHAND from availability AA where  F.items_itemcode=AA.itemcode)as Availability,' ' AS ORDEN, f.favorito AS FAVORITO" _
&"  FROM CUSTOMERS C" _
&"  INNER JOIN  FAVORITO F" _
&"  ON C.ACCOUNT=F.CUSTOMERS_ACCOUNT" _
&"  INNER JOIN ITEMS I " _
&"  ON F.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  LEFT JOIN AVAILABILITY A" _
&"  ON I.ITEMCODE=A.itemcode" _
&"  LEFT JOIN PRICE_BREAK pb" _
&"  ON I.ITEMCODE=PB.ITEMCODE" _
&"  WHERE  customer=" & customer _
&"  AND I.ITEMCODE NOT IN (" _
&"  select DISTINCT(I.ITEMCODE) " _
&"  FROM CUSTOMERS C" _
&"  INNER JOIN  ORDEN O" _
&"  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
&"  INNER JOIN ITEMS I " _
&"  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  INNER JOIN FAVORITO F " _
&"  ON c.account=f.customers_account" _
&"  AND F.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  LEFT JOIN AVAILABILITY A" _
&"  ON I.ITEMCODE=A.itemcode" _
&"  LEFT JOIN PRICE_BREAK pb" _
&"  ON I.ITEMCODE=PB.ITEMCODE" _
&"  WHERE  C.account=" & customer _
&"  )" _
&"  union all" _
&"  select distinct(c.account) as customer,(Select id_break from price_break pp where o.id_break=pp.id_break) As id_break,(Select quantity from price_break pp where o.id_break=pp.id_break) As quantity,i.category,i.itemcode as ITEMCODE,I.description AS DESCRIPTION, I.pricing AS PRICING,(select price from price_break pp where F.id_break=pp.id_break)as price_break,A.ONHAND as Availability,o.cantidad AS ORDEN,  f.favorito AS FAVORITO" _
&"  FROM CUSTOMERS C" _
&"  INNER JOIN  ORDEN O" _
&"  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
&"  INNER JOIN ITEMS I " _
&"  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  INNER JOIN FAVORITO F " _
&"  ON c.account=f.customers_account" _
&"  AND F.ITEMS_ITEMCODE=I.ITEMCODE" _
&"  LEFT JOIN AVAILABILITY A" _
&"  ON I.ITEMCODE=A.itemcode" _
&"  LEFT JOIN PRICE_BREAK pb" _
&"  ON I.ITEMCODE=PB.ITEMCODE" _
&"  WHERE  C.account=" & customer _
&"  union all" _
&"  SELECT CUSTOMER,id_break,quantity,category,ITEMCODE,DESCRIPTION,PRICING,PRICE_BREAK,AVAILABILITY,ORDEN,FAVORITO" _
&"  FROM" _
&"  (SELECT  DISTINCT(I.ITEMCODE) AS ITEMCODE,(Select id_break from price_break pp where f.id_break=pp.id_break) As id_break,pb.quantity,i.category as category,(select customers_account from orden where pb.id_break=orden.id_break) as customer , I.description AS DESCRIPTION, I.pricing AS PRICING ,pb.price aS price_break ,A.ONHAND as Availability ,' ' orden,(select favorito from FAVORITO where pb.id_break=favorito.id_break AND i.itemcode=favorito.items_itemcode AND CUSTOMERS_ACCOUNT="&customer&") As FAVORITO" _
&"    FROM  ITEMS I" _
&"  LEFT join price_break pb" _
&"  on i.itemcode=pb.itemcode" _
&"  LEFT JOIN AVAILABILITY A" _
&"  ON I.ITEMCODE=A.ITEMCODE" _
&"  LEFT JOIN FAVORITO  F" _
&"  ON I.ITEMCODE=F.ITEMS_ITEMCODE" _
&"  where customer IS NULL" _
&"  )A" _
&"  where favorito is not 1" _
&"  AND  ITEMCODE NOT IN("&otrac&")"
Return cadena
End Sub

Sub cadenaR (a As String)
Dim cadenaRR As String=""
cadenaRR="Select i.itemcode As ITEMCODE" _
& "  FROM CUSTOMERS C" _
& "  INNER JOIN  ORDEN O" _
& "  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
& "  INNER JOIN ITEMS I " _
& "  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  LEFT JOIN AVAILABILITY A" _
& "  ON I.ITEMCODE=A.itemcode" _
& "  LEFT JOIN PRICE_BREAK pb" _
& "  ON I.ITEMCODE=PB.ITEMCODE" _
& "  WHERE  customer="&a _
& "  AND I.ITEMCODE NOT IN (" _
& "  select DISTINCT(I.ITEMCODE) " _
& "  FROM CUSTOMERS " _
& "  INNER JOIN  ORDEN O" _
& "  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
& "  INNER JOIN ITEMS I " _
& "  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  INNER JOIN FAVORITO F " _
& "  ON c.account=f.customers_account" _
& "  AND F.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  LEFT JOIN AVAILABILITY A" _
& "  ON I.ITEMCODE=A.itemcode" _
& "  LEFT JOIN PRICE_BREAK pb" _
& "  ON I.ITEMCODE=PB.ITEMCODE" _
& "  WHERE  C.account="&a _
& "  )" _
& "  UNION ALL" _
& "  select i.itemcode as ITEMCODE" _
& "  FROM CUSTOMERS C" _
& "  INNER JOIN  FAVORITO F" _
& "  ON C.ACCOUNT=F.CUSTOMERS_ACCOUNT" _
& "  INNER JOIN ITEMS I " _
& "  ON F.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  LEFT JOIN AVAILABILITY A" _
& "  ON I.ITEMCODE=A.itemcode" _
& "  LEFT JOIN PRICE_BREAK pb" _
& "  ON I.ITEMCODE=PB.ITEMCODE" _
& "  WHERE  customer="&a _
& "  AND I.ITEMCODE NOT IN (" _
& "  select DISTINCT(I.ITEMCODE) " _
& "  FROM CUSTOMERS " _
& "  INNER JOIN  ORDEN O" _
& "  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
& "  INNER JOIN ITEMS I " _
& "  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  INNER JOIN FAVORITO F " _
& "  ON c.account=f.customers_account" _
& "  AND F.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  LEFT JOIN AVAILABILITY A" _
& "  ON I.ITEMCODE=A.itemcode" _
& "  LEFT JOIN PRICE_BREAK pb" _
& "  ON I.ITEMCODE=PB.ITEMCODE" _
& "  WHERE  C.account="&a _
& "  )" _
& "  union all" _
& "  select i.itemcode as ITEMCODE" _
& "  FROM CUSTOMERS C" _
& "  INNER JOIN  ORDEN O" _
& "  ON C.ACCOUNT=O.CUSTOMERS_ACCOUNT" _
& "  INNER JOIN ITEMS I " _
& "  ON O.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  INNER JOIN FAVORITO F " _
& "  ON c.account=f.customers_account" _
& "  AND F.ITEMS_ITEMCODE=I.ITEMCODE" _
& "  LEFT JOIN AVAILABILITY A" _
& "  ON I.ITEMCODE=A.itemcode" _
& "  LEFT JOIN PRICE_BREAK pb" _
& "  ON I.ITEMCODE=PB.ITEMCODE" _
& "  WHERE  C.account="&a

Return cadenaRR
End Sub

and this is how this queries are called

dim query as string
query = price_list_general(account)
dbcursor = sql1.execquery(query)

I will greatly appreciate all the help i can get on this.

In advance thank you very much for the help guys.

Walter

 

[LucaMs]

That query is illegible and almost certainly absurd: you have 4 tables only and 3,450,934,573,457 joins!!!

the data from the first 4 columns is retrieved from one table called items, the data from the 5th columns needs to be pulled from another table called onhand (How? Which fields it contains?)
from the order table and from the favorite table. (Which fields they contain?)


What are, in each table, the key fields? and foreign keys? and indexes?

 

[MaFu]

Please post the structure of the 4 tables and the relationship.

 

[Jmu5667]

I have some experience with SQL etc, if you are dealing with a large amount of data the first suggestion is a server side cursor and forward only, and yes the structure of the table would be helpful. Also and sample of the resulting data would help in defining the query.

 

[walterf25]

Hi guys thanks for your replies you guys can download the database with all the information from here you guys can see the foreign keys and key fields on each table, i really appreciate you guys' help please let me know if you guys need any additional information.

https://www.dropbox.com/s/df0tw56og5ix5dy/mobileapp.db

Thanks,
Walter

 

[Reviewnow]

Here is the solutions that I have come up with for you and only requires a single query

Select
account
,itemcode
,category
,description
-- when the order count > the price break quanity set the price to the new price
,case when orders > (select quantity from price_break where itemcode = t3.itemcode) then (select price from price_break where itemcode = t3.itemcode) else price end as price
,availability
,orders
,favorite from

(
Select t2.account
,items.itemcode
,items.category
,items.description
,items.pricing as price
,availability.onhand as Availability

-- Select order count for this client and item_code
,(select count(items_itemcode) from orden where customers_account  = t2.account and items_itemcode = items.itemcode) as orders
--is product a favorite for this client
,(select favorito from favorito where customers_account  = t2.account and items_itemcode = items.itemcode) as favorite

from
(
-- select account items_itemCode and  all orders for client
Select Account,items_itemcode from
(
--Make sure the customer has orders in the order table
Select account from Customers Where account IN ( Select customers_account from Orden INNER JOIN Customers on Customers.account=orden.customers_account)
) as t1
inner join orden on t1.account=orden.customers_account
) as t2
inner join items on t2.items_itemcode = items.itemcode
inner join Availability on  t2.items_itemcode = availability.itemcode)
as t3
--uncoment for where statement and only select the client needed
-- where t3.account = incoming.customer

 

[walterf25]

Here is the solutions that I have come up with for you and only requires a single query

Select
account
,itemcode
,category
,description
-- when the order count > the price break quanity set the price to the new price
,case when orders > (select quantity from price_break where itemcode = t3.itemcode) then (select price from price_break where itemcode = t3.itemcode) else price end as price
,availability
,orders
,favorite from

(
Select t2.account
,items.itemcode
,items.category
,items.description
,items.pricing as price
,availability.onhand as Availability

-- Select order count for this client and item_code
,(select count(items_itemcode) from orden where customers_account  = t2.account and items_itemcode = items.itemcode) as orders
--is product a favorite for this client
,(select favorito from favorito where customers_account  = t2.account and items_itemcode = items.itemcode) as favorite

from
(
-- select account items_itemCode and  all orders for client
Select Account,items_itemcode from
(
--Make sure the customer has orders in the order table
Select account from Customers Where account IN ( Select customers_account from Orden INNER JOIN Customers on Customers.account=orden.customers_account)
) as t1
inner join orden on t1.account=orden.customers_account
) as t2
inner join items on t2.items_itemcode = items.itemcode
inner join Availability on  t2.items_itemcode = availability.itemcode)
as t3
--uncoment for where statement and only select the client needed
-- where t3.account = incoming.customer

Thank you so much for this query, it works great, but this only returns 5 records, in reality i need to show all the records from the price list table, and when an amount is entered on any record in the order column it will create that order in the Order tab, when any checkbox is checked on any record it should add that record to the favorites tab in the tabhost.

How can i accomplish this?

Thanks,
Walter

 

[Reviewnow]

So basically you only want to place an order for a specific client and show all the product that the customer can order? and the quantity on hand > 0
and this will be your customer order form ?

if this is the case use this and hold the ordering customeraccount in a variable pass the variable when you decide to create the order

Select items.itemcode,category,description,pricing as price,availability.onhand,cast(0 as numeric) as order_quanity,0 as favorite from items
inner join availability on items.itemcode = availability.itemcode and availability.onhand > 0
order by category,description

 

[walterf25]

So basically you only want to place an order for a specific client and show all the product that the customer can order? and the quantity on hand > 0
and this will be your customer order form ?

if this is the case use this and hold the ordering customeraccount in a variable pass the variable when you decide to create the order

Select items.itemcode,category,description,pricing as price,availability.onhand,cast(0 as numeric) as order_quanity,0 as favorite from items
inner join availability on items.itemcode = availability.itemcode and availability.onhand > 0
order by category,description

Yes basically the listview will hold all the +10000 items, when the sales person enters a quantity in the order column, that specific record needs to be inserted into another table named 'Orders' and also when the checkbox is checked that specific row needs to be inserted into a different table named 'favorites'. Will this query be able to do this?

Thanks again,
Walter

 

[Reviewnow]

yes you will have to have a button to place the order, once you have the customeraccount in the variable

parse your list of ordered products where the order_quantity > 0

then build a map of all the products ordered

once you have the customeraccount,itemcode,order_quanity

do a lookup on the price_break table and see if the price has to be changed before placing the order

I would suggest using the dbutils class library and the insertmaps sub procedure

 

[eps]

Listview, really?

 

[walterf25]

Listview, really?

what do you mean, ListView, Really?

 

[eps]

what do you mean, ListView, Really?

It's just that you mention checkboxes and Listview doesn't handle that very well.

Custom Listview is the way to go.

Phil