Both queries are complaining because MSSQL insists that all derived tables
(that is, subselects used as tables) be aliased with an "AS xxxx" clause.
In your first query, the inner query is aliased OK (as "maxver"), but the
outer one (which ends just before the WHERE) is not aliased.
In the second, we see the same thing -- the inner query is aliased as
"maxver", but the outer one (which ends just before the "ON") is not
aliased.
In your code, you've obviously tried to alias the selects as "select_1" and
"select_2". I think the issue is how the mapper constructs the query as a
derived table. It should put the alias on the inner query it makes, not on
the outer query, no?
Rick
On 5/11/07, Chris Perkins <[EMAIL PROTECTED]> wrote:
>
>
> On May 10, 1:53 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> > the second version is not going to work because you are expressing
> > the joins and foreign keys in terms of the underlying tables, not the
> > relations to which the mappers are bound (i.e. T1_select,
> > T2_select). so the mapper cant do anything with that (more
>
> OK. I guess I find it surprising because I expeced that:
> T1.c.id is T1_select.c.id
> That is, I expected the select to just reference the Column objects
> from the table, not to have copies of them, so I thought the two joins
> would be identical.
>
> But now I have another problem - I've fixed my mappers to join
> properly, but I'm getting SQL that mssql 2005 chokes on. Here's a
> condensed version of my original example:
>
> ########
> from sqlalchemy import *
>
> meta = MetaData()
>
> T = Table('T', meta,
> Column('id', Integer, primary_key=True),
> Column('ver', Integer, primary_key=True),
> )
>
> maxvers = select(
> [T.c.id.label('mvid'), func.max(T.c.ver).label('maxver')],
> group_by=[T.c.id],
> )
>
> # Try two different ways:
> select_1 = select(
> columns = [T],
> from_obj = [T, maxvers],
> whereclause = and_(maxvers.c.mvid==T.c.id,
> maxvers.c.maxver==T.c.ver),
> ).alias('select_1')
>
> select_2 = select(
> columns = [T],
> from_obj = [join(T, maxvers,
> onclause=and_(maxvers.c.mvid==T.c.id, maxvers.c.maxver==T.c.ver))],
> ).alias('select_2')
>
> class One(object): pass
> class Two(object): pass
> m1 = mapper(One, select_1)
> m2 = mapper(Two, select_2)
>
>
> e = create_engine('mssql://sa:[EMAIL PROTECTED]/test')
> meta.create_all(e)
> s = create_session(bind_to=e)
> e.echo = True
>
> # Both of the following fail on MSSQL 2005
> try:
> s.query(m1).select()
> except Exception, e:
> print e
>
> try:
> s.query(m2).select()
> except Exception, e:
> print e
>
> ########
>
> The first error is "Incorrect syntax near the keyword 'WHERE'" for:
> SELECT select_1.ver AS select_1_ver, select_1.id AS select_1_id
> FROM (SELECT [T].id AS id, [T].ver AS ver
> FROM [T], (SELECT [T].id AS mvid, max([T].ver) AS maxver
> FROM [T] GROUP BY [T].id)
> WHERE mvid = [T].id AND [T].ver = maxver) AS select_1 ORDER BY
> select_1.id
>
> The second is "Incorrect syntax near the keyword 'ON'" for:
> SELECT select_2.ver AS select_2_ver, select_2.id AS select_2_id
> FROM (SELECT [T].id AS id, [T].ver AS ver
> FROM [T] JOIN ((SELECT [T].id AS mvid, max([T].ver) AS maxver
> FROM [T] GROUP BY [T].id)) ON mvid = [T].id AND [T].ver = maxver) AS
> select_2 ORDER BY select_2.id
>
> I think that for the second one, it's just complaining about the
> doubled brackets around the nested query. If I paste it into the SQL
> Server UI and run it, it works only if I remove the extra set of
> brackets.
>
> I have no idea what's wrong with the first one. If I paste that one
> into SQL Server UI, it runs correctly, but it rewrites it to this:
> SELECT ver AS select_1_ver, id AS select_1_id
> FROM (SELECT T.id, T.ver
> FROM T INNER JOIN
> (SELECT id AS
> mvid, MAX(ver) AS maxver
> FROM T AS
> T_1
> GROUP BY id) AS
> derivedtbl_1 ON T.id = derivedtbl_1.mvid AND T.ver =
> derivedtbl_1.maxver) AS select_1_1
> ORDER BY select_1_id
>
>
> Any idea how I can write my selects to work against SQL server?
>
> Thanks,
>
> Chris Perkins
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
