I would like to use the WITH clause against my sqlite database in order to join a query with itself. The query works fine when tested in SqliteStudio v3.0.2 but fails when called in web2py using executesql. The error is "error near 'WITH'". I assume that means the version of the sqlite driver with web2py 2.9.12 doesn't support "WITH" clauses. Is this true?
[By the way, the reason I want to join a query with itself is to compute cumulatives. For example if I have a table "TableSales" with columns "time_period", "product_id", and "sales" I want to know cumulative sales by time period by product_id. In sqlite, I can do this as WITH sls AS (SELECT time_period,product_id,sales FROM TableSales) SELECT s1.product_id,s1.time_period,max(s1.sales) as sales,sum(s2.sales) as cumsales FROM sls AS s1 INNER JOIN sls AS s2 ON (s1.product_id=s2.product_id) WHERE s1.time_period>=s2.time_period GROUP BY s1.product_id,s1.time_period; The WITH clause allows me to specify the underlying query just once but reference it twice in the FROM clause. In my application, the underlying query is quite complicated.] Thanks for any help and my compliments on a great web framework. I have tried 6 other frameworks over the past decade: web2py is the best! -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
