> select foo.ric, tm.times_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct
> ric from ticks) as
> foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and
> tk.tick_time <
> (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by
> tm.times_time,
> foo.ric order by tm.times_time;
>
> I get a error message like this:
>
> ERROR: invalid reference to FROM-clause entry for table "foo"
> HINT: There is an entry for table "foo", but it cannot be referenced from
> this part of the
> query.
>
> Can you help me with this?
I will try, but to start with, to help us, when you have a difficult query to
solve, you should
simplify your query as much a possible. This way we can more quickly see what
you are intending
verses the problem you are having.
1 tip: (select distinct ric from ticks)
I think that you will find that:
(select ric from ticks group by ric)
is much faster than using the distinct.
The error in the query that I see is that you are using foo as a criteria in
the ON syntax. This
will not work. To illistrate:
A,B join C
ON (B.id = C.id) --ON syntax only works with joins
AND (B.id2 < C.id) --The And is still part of the ON syntax
--you can not reference A since it is not joined
Where
A.id = B.id --you can only specify a non-joined tables contrainst
AND
A.id2 < C.id2
; --in the where clause
I hope this helps.
Regards,
Richard Broersma JR.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
