Thank you *so* much...
And apologies for not having included the object set up ... I thought it'd obscure more than it showed... But for the record, the `generate_series` and `row_number() over()` are spot on :D On Monday, 15 July 2013 18:58:00 UTC+1, Rob wrote: > > (using sqlalchemy version 7.10 & postgres), I'm attempting to join a CTE > twice in the same select statement. > > see here <http://www.sqlfiddle.com/#!12/c487e/14> for a SQLFiddle, which > shows the full select statement which I'm trying to build but essentially > the problem I'm facing is how to create aliases for the `WITH` such that > a second join is able to reference the first -- the emboldened 'AS > <alias>' below, highlights the problem. > > WITH semester_days AS ( ... blah ... ) > SELECT ... blah ... > FROM > students as S > JOIN > semester_days *as SD_start* > On SD_start.day_date = S.start_date > JOIN > semester_days *as SD_end* > On SD_end.day_number = SD_start.day_number + (7 * S.n_weeks) > > Attempting to use: > my_cte = select([ ... ]).cte(name='semester_days') > followed by: > cte_start = my_cte.alias('start_date') > cte_end = my_cte.alias('end_date') > > ... in ether a .select_from() appended to the select clause, or in a > `from_obj` within the clause, simply blows the `with` statement off the top > of the select. (if that makes any sense) > > Is there any way to accomplish this? > > Many thanks, > Rob > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
