I am sorry the DAL does not support this. You will have to use db.executesql(....)
On Monday, 7 December 2015 12:05:03 UTC-6, [email protected] wrote: > > 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.
