Thanks. I wrapped it as ' (<original_sql>) as foo' as Sybase needs a
name for the derived table.You have helped to get primary key and
index introspection working on Sybase!
One interesting and useful bit of information is that one can't use
placeholders in the WHERE clause for anything other than column
values: Pyodbc over FreeTDS barfs with an unknown type error as it
does not know the datatype of functions.
s = sql.select([sysindexes.c.name.label('index_name'),
sysindexes.c.status,
index_col(sql.func.object_name
(sysindexes.c.id), sysindexes.c.indid, nums.c.n).label('column_name'),
nums.c.n.label('column_ordinal'),
],from_obj=[sysindexes.join(nums,nums.c.n <=
sysindexes.c.keycnt).join(sysobjects)]).where(sysobjects.c.name
==table.name).order_by(nums.c.n)
On Mar 3, 5:03 pm, Rick Morrison <rickmorri...@gmail.com> wrote:
> Seems to me the issued SQL would work if the innermost query (the UNION
> query) was phrased as a subquery. Have you tried simply wrapping the literal
> SQL text in parenthesis to force it into a subquery like this?
>
> nums = sql.select(['n'], from_obj=sql.text(r"""(SELECT 1 as n
> UNION ALL SELECT 2
> UNION ALL SELECT 3
> UNION ALL SELECT 4
> UNION ALL SELECT 5
> UNION ALL SELECT 5
> UNION ALL SELECT 6
> UNION ALL SELECT 7
> UNION ALL SELECT 8
> UNION ALL SELECT 9
> UNION ALL SELECT 10
> UNION ALL SELECT 11
> UNION ALL SELECT 12
> UNION ALL SELECT 13
> UNION ALL SELECT 14
> UNION ALL SELECT 15
> UNION ALL SELECT 16) """)).alias('nums')
>
> Alternatively, (if SQLA supports aliasing of sql text literals, I haven't
> tried this), you could alias the innermost query, and use that as virtual
> table:
>
> uq = sql.text(r"""SELECT 1 as n
> UNION ALL SELECT 2
> UNION ALL SELECT 3
> UNION ALL SELECT 4
> UNION ALL SELECT 5
> UNION ALL SELECT 5
> UNION ALL SELECT 6
> UNION ALL SELECT 7
> UNION ALL SELECT 8
> UNION ALL SELECT 9
> UNION ALL SELECT 10
> UNION ALL SELECT 11
> UNION ALL SELECT 12
> UNION ALL SELECT 13
> UNION ALL SELECT 14
> UNION ALL SELECT 15
> UNION ALL SELECT 16""").alias('uq')
>
> nums = sql.select(['n'], from_obj=uq).alias('nums')
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
