Mark wrote:
> Hi,
>
> I have trouble forming the below SQL statement using SQLAlchemy orm:
>
> select *
> from (
> select
> c.id class_id,
> sum(decode(e.enrollment_status_id, 2, 1,null,0,0)) total,
> c.enrollment_limit enrollment_limit
> from classes c, enrollment e
> where c.id = e.class_id(+)
> group by c.id, c.enrollment_limit
> )
> where enrollment_limit <= total
>
> I know that center portion should be a subquery of sorts, but I don't
> know how to convert the decode into valid SQLalchemy ORM syntax.
a function like "decode" would be rendered using the "func" construct,
i.e. func.DECODE(*args...).
>
> I tried using SQL Expressions and this was what I got:
>
> connection = tkengine.connect()
> s = text("""\
> select c.id class_id, sum(decode(e.enrollment_status_id,
> 2, 1,null,0,0)) total, c.enrollment_limit enrollment_limit
> from classes c, enrollment e
> where c.id = e.class_id(+)
> group by c.id, c.enrollment_limit
> """)
> stmt = connection.execute(s)
>
> I would like to then use this subquery to obtain a list of classes
> that have enrollment_limit greater than (>) total.
>
> Thanks.
>
> --
> 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.
>
>
--
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.
