robert -
try using the flag "correlate=False" in the Select which you want to
be fully self-contained.
(also post the full code, since I think using the alias for the
"yearly" table should be working also)
On Apr 15, 2006, at 9:43 PM, Robert Leftwich wrote:
I'm trying to setup a complicated query that needs to use a
correlated subquery in the WHERE clause and I can't find the right
incantation for SA to generate the query I want.
A simplified example is that I have a table, lets call it 'daily'
which has daily entries in it for a number of entities and another
table 'yearly' which has, surprisingly, yearly entries for each of
the entities in it. However the yearly dates are not uniform and
can in fact have two entries per year, so I'm needing to pick the
latest entry for each year that is less than or equal to the
current date of the daily entry (so I don't get the 2006 yearly
entry when I'm looking at a 2005 daily entry for example).
firstly, what I need is something along the lines of
SELECT daily.code from daily d, yearly y1
WHERE y1.id = d.id
AND y1.date =
(SELECT max(y2.date)
FROM yearly y2
WHERE y2.date <= d.date
AND (date_part('year', d.date) - date_part('year', y2.date)
<= 1))
which I can achieve via text() w/o any problem, but if I try to
assemble the query using SA, via select and alias, it always adds
the aliased table y2 to the outer query FROM statement, causing
Postgres to throw an error because the inner query is using an
aggregate, i.e. the SQL from
yearly_2 = yearly_tbl.alias('yearly_2')
s = select([func.max(yearly_2.c.date))], and_(yearly_2.c.date <=
daily.c.date, func.date_part('year', daily.c.date) - func.date_part
('year', yearly_2.c.date) <= 1))
...
ends up as:
SELECT daily.code from daily d, yearly y1, yearly y2
WHERE y1.id = d.id
AND y1.date =
(SELECT max(y2.date)
WHERE y2.date <= d.date
AND (date_part('year', d.date) - date_part('year', y2.date)
<= 1))
which is not valid (at least in postgres).
So my first question is: is there a way to force the inner query to
be fully self-contained, including the FROM?
Secondly, the real criteria is (much) more complicated again and I
need to be able to look up 2 yearly entries, the most recent and
one from 5 years prior to the most recent which means I need a
second inner query to find the older entry, so, the solution for
the first question needs to work for at least one more inner query.
Any assistance gratefully accepted.
Robert
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting
language
that extends applications into web and mobile media. Attend the
live webcast
and join the prime developer group breaking into this new coding
territory!
http://sel.as-us.falkag.net/sel?
cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
