robert -
this example gives you the output you want. not sure why yours isnt
working:
from sqlalchemy import *
daily = table('daily',
column('id'),
column('code'),
column('date'),
)
yearly = table('yearly',
column('id'),
column('date'),
)
yearly_2 = yearly.alias('yearly_2')
subquery = select(
[func.max(yearly_2.c.date).label('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
)
)
sel = select([daily, yearly], and_(yearly.c.id==daily.c.id,
yearly.c.date==subquery))
print str(sel)
SELECT daily.id, daily.code, daily.date, yearly.id, yearly.date
FROM daily, yearly
WHERE yearly.id = daily.id AND yearly.date = (SELECT max
(yearly_2.date) AS date
FROM yearly AS yearly_2
WHERE yearly_2.date <= daily.date AND (date_part(:date_part,
daily.date) - date_part(:date_pa_1, yearly_2.date)) <= :literal)
On Apr 16, 2006, at 6:09 PM, Robert Leftwich wrote:
Michael Bayer wrote:
try using the flag "correlate=False" in the Select which you want
to be fully self-contained.
Unfortunately, I need it to be correlated (it is relative to the
current daily date), it just needs its own FROM statement.
(also post the full code, since I think using the alias for the
"yearly" table should be working also)
Ok, I'll try and extract a self-contained working example (as it is
part of complex query builder where the user can pick and choose
various business criteria that eventually resolve to constructed SA
queries).
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
