SQLite: "Variable" Names in Views?

Databases
1

Hi there,

Is it possible to address a freshly defined SQLite view field by its name in the same query?
Like in this one which is getting ridiculously unreadable:

rs = app.db.sqlselect ("CREATE View "+app.extendedCustomers+" as select *, "+ _ "case when status not like ""cancelled"" then (item_total) else 0 end as RealTotal, "+ _ "case when status not like ""cancelled"" then sum (case when status not like ""cancelled"" then (item_total) else 0 end) else 0 end as Umsatz, "+ _ "case when status not like ""cancelled"" then (item_discount) else 0 end as RealDiscount, "+ _ "case when status not like ""cancelled"" then sum (case when status not like ""cancelled"" then (item_discount) else 0 end) else 0 end as Gesamtrabatt, "+ _ "(sum (case when status not like ""cancelled"" then (item_total) else 0 end) - case when sum (case when status not like ""cancelled"" then (item_discount) else 0 end) Not Null then sum (case when status not like ""cancelled"" then (item_discount) else 0 end) else 0 end ) As Rabattumsatz, "+ _ "count(*) As Bestellungen from customers as c join orders as O , items as i, order_items_link as oil, Parzelle_orderitem_link AS p on c.customer_ID = O.customer_id and i.ID = oil.item_nameID and oil.order_id = o.order_id and p.order_item_ID = oil.ID group by c.customer_id") success = not app.db.error

I thought I could make the 3rd line rather look like

"case when status not like ""cancelled"" then sum  (RealTotal) else 0 end as Umsatz, "+ _

but if I try, SQLite tells me it doesn’t know any field RealTotal. The query above works, but I am afraid what will happen if I have to modify it at some point in the future …

2

It’s a little hard to decipher as there aren’t any table aliases in the select clause, but assuming status and item_total are coming from the orders table, you could create a view that just does that simple case statement and returns the orders.id and “RealTotal”, and then join to that view via the id to get the RealTotal rather than use the case statement multiple times.

In that same view you could do the RealDiscount calculation too.

Not sure about the speed of using a view in comparison to the case statement though, you’ll need to experiment with that, but I would expect it to be pretty good.

3

And I would only create a view as a convenience… where it was used in multiple other queries or packages. Not as a single place/single use solution… For that I’d suggest a table with the appropriate joins/unions and where required, indexes.

4

Thanks, you two! Yes, as a view is more or less a fixed select statement, performance is really ok. The view on a view was something I had in mind too – I was afraid performance could suffer but it’s probably faster than calculating db values on the Xojo side. I’ll experiment a bit with it.

And indeed, Dave: This view is basically the main table I use for an application, more or less a reconstruction of the original Excel table which I normalized into different SQLite tables. So it’s used all the time. But why shouldn’t one use views for lesser tasks?

5

Views are fine
They just usually are NOT updateable so they often should be treated as “read only”