I understand what you are saying about this being an uphill battle on mssql.
If we ignore the CTE for the moment and pretend that all_dates is a regular table, shouldn't there be no difference for generation. You can subquery as needed and all that really needs to happen is to prepend the CTE to the rest of the SQL before execution. Maybe this approach isn't generally applicable, CTE's already make my head hurt :) Searching the mailing list found a thread ( http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f380e277af/fb179a515bf48868) where you used a regular expression to shim in a special comment. This seems hackish but could a similar approach work on mssql? Is it possible to hook into only the top-level/last Select's compile()? Thanks for all of your help. -Marc On Thu, Aug 18, 2011 at 1:37 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Aug 18, 2011, at 1:28 PM, Marc DellaVolpe wrote: > > > There seems to be a problem with the custom compilation. > > > > >>> print Session.query(Date).order_by(Date.date.desc()) > > > > WITH all_dates(date) AS ( > > SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, > :step, date) AS "DATEADD_1" > > FROM all_dates > > WHERE DATEADD(dd, :step, date) <= CAST(:stop AS DATETIME) > > ) > > SELECT * FROM all_dates > > OPTION (MAXRECURSION 0) > > Heh. This is what I was saying - Query is going to produce "SELECT x, y, z > FROM (your custom statement) ORDER BY <order by>". You blow away that > SELECT on the outside you lose the ORDER BY also and anything else. The > only path here would involve a much more concerted effort with the > overriding of the Select construct to pull off everything it does and apply > it to the thing that's being wrapped. Each new thing you'd like to do, > like JOIN to it, etc., means your magic compiler would need to work it out. > > The key issue here is that SQL Server doesn't supply a generically useful > form of CTE since it cannot be nested. Very uphill. > > > -- > 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. > > -- 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.
