On Thu, 19 Dec 2013 16:56:18 -0500
Simon <turne...@gmail.com> wrote:
> select datetime(bucket*plen,'unixepoch','-5 hour') dt,
> (select price from last_trades where tid=opentr) open,
Unless I misread it, that subquery returns the price for every row in
last_trades whose tid is equal to opentr. If that's not a scalar
result, you should get an error, and maybe not the one you want.
I think the actual error is
> from (
> select cast(date/periodlen as int) bucket,periodlen,*
> from last_trades t, (select 5*60 periodlen) t2
> where isprimary='Y' ) GROUP by bucket
I bet the SQL parser swallows GROUP as the name of your virtual table.
(A case for neater formatting?) It should be
where isprimary='Y' ) AS foo GROUP BY bucket
I think you'll get a more readable query, and maybe find the error, by
recasting the "outer" query as a join
select ... , O.price as openpr, C.price as closepr
from ( ... ) AS T
join last_trades as O
on O.tid = T.opentr
join last_trades as C
on C.tid = T.closetr
If your table doesn't enforce unique tid, I would use
from T
join (select min(price) as price, count(*) as q
from last_trades) as O
on O.tid = T.opentr
join (select min(price) as price, count(*) as q
from last_trades) as C
on C.tid = T.closetr
and watch for rows
where O.q > 1 OR C.q > 1
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
