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
