CODEWITHSUNDEEP
pythonsqlalchemy
Ameet S M
Ameet S M

Reputation: 190

SQLAlchemy: Logical Operators with case statement

Here is my mssql code snippet

SELECT
Sum((Case  When me.status not in ('CLOSED','VOID') and me.pc_cd in ('IK','JM')
Then 1 else 0 end)) as current_cd
from
ccd_pvc me with(nolock)

How would i use the and operator with case statement if i write the above statement in sqlalchemy.

I have tried doing this but did not work

case([and_((ccd_pvc.status.in_(['CLOSED', 'VOID']),ccd_pvc.pc_cd.in_(['IK','JM'])),
literal_column("'greaterthan100'"))])

I have searched through the sqlalchemy documentation but did not find the info on using logical operators with case statement.

The link has some info on this.

Upvotes: 0

Views: 2003

Answers (1)

van
van

Reputation: 76992

This should get you started:

ccd_pvc = aliased(CcdPvc, name="me")

expr = func.sum(
    case([(and_(
        ccd_pvc.status.in_(['CLOSED', 'VOID']),
        ccd_pvc.pc_cd.in_(['IK', 'JM'])
    ), 1)], else_=0)
).label("current_cd")

q = session.query(expr)

Upvotes: 3

Related Questions