I am interested in any feedback on the code at
https://gist.github.com/1156683 . The code works for me but it's a hacked
solution and I'm not sure if this will work generally. I am not sure if
there is a better way to handle gather the CTE's, I simply tacked the found
CTE onto the compiler object. Also this should support multiple CTE's.
print Date.range('2011-01-01 00:00:00', '2012-02-01
00:00:00').filter("date>='2011-01-15 00:00:00'").order_by(Date.date.desc())
WITH all_dates(date) AS (
SELECT CAST(? AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, ?, date)
AS [DATEADD_1]
FROM all_dates
WHERE DATEADD(dd, ?, date) <= CAST(? AS DATETIME)
)
SELECT date AS date
FROM (SELECT * FROM all_dates) as x
WHERE date>='2011-01-15 00:00:00' ORDER BY date DESC
OPTION (MAXRECURSION 0)
On Thu, Aug 18, 2011 at 2:20 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Aug 18, 2011, at 2:01 PM, Marc DellaVolpe wrote:
>
> 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 :)
>
>
> yeah that could work as well but its not clear to me if the CTE can be up
> above everything, then referenced within a nested structure like that. I
> haven't really worked with CTEs to have that level of experience.
>
>
> 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()?
>
>
> if it works, then yes you'd get your Select compiler to dig in, look for
> CTEs, put the WITH all the way on top, assuming the Select is the
> "outermost". You can tell if you're the "outermost" select by checking
> "bool(compiler.stack)", which is a stack of SELECT constructs.
>
>
> --
> 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.
