In general, I think it's much better (performance and logic) to do all you can in SQL, without passing values out of SQL results, into your non-SQL code, then re-injecting back into another SQL query etc. Having said that, I'm not quite sure why you are doing what you're doing, so I can't attempt to offer the best solution.
On 07/02/2010, at 1:20 AM, personalt wrote: > I was looking to store this whole query inside a view and just query the view > from outside sqlite. > > Is there a way to inside a large sql statement to store a intermediate value > which could be used later in that query? You could create views, something like this: create view MySubQuery as select kwhcost1 from applications ; create view MyQuery as select monitordata_hourly.deviceaddress , Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh , Round(Sum(monitordata_hourly.ch1kwh),3)*(select * from MySubQuery) AS SumOfch1kwh_cost from monitordata_hourly where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30 days') group by monitordata_hourly.deviceaddress ; select * from MyQuery ; or you could set up a table to hold the intermediate variables: create table Variables ( Name text unique , Value ) ; insert or replace into Variables(Name, value) select 'kwhcost', kwhcost1 from applications ; select monitordata_hourly.deviceaddress , Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh , Round(Sum(monitordata_hourly.ch1kwh),3)*(select value from Variables where Name = 'kwhcost') AS SumOfch1kwh_cost from monitordata_hourly where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30 days') group by monitordata_hourly.deviceaddress ; Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users