I'm trying to produce Core expression corresponding to this SQL: https://dba.stackexchange.com/a/122116/47472
However, this expression fails:
q = select([
component,
base_vee,
case(
[
(coalesce(base_vee.c.affidavit, '') == '')
],
else_=True).label('emptyish')
]). \
where(
and_(
component.c.id.in_(comp_ids),
component.c.id == base_vee.c.component_id,
base_vee.c.type == 'typical',
)
)
Exception:
File "/export/home/myuser/mysite/mysite/afile.py", line 527, in afunc
else_=True).label('emptyish')
File "<string>", line 2, in case
File "/export/home/myuser/mysite/ve/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 2187, in __init__
_literal_as_binds(r)) for (c, r) in whens
File "/export/home/myuser/mysite/ve/lib/python2.7/site-packages/sqlalchemy/sql/operators.py", line 343, in __getitem__
return self.operate(getitem, index)
File "/export/home/myuser/mysite/ve/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 739, in operate
return op(self.comparator, *other, **kwargs)
File "/export/home/myuser/mysite/ve/lib/python2.7/site-packages/sqlalchemy/sql/operators.py", line 343, in __getitem__
return self.operate(getitem, index)
File "<string>", line 1, in <lambda>
File "/export/home/myuser/mysite/ve/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 60, in operate
return o[0](self.expr, op, *(other + o[1:]), **kwargs)
File "/export/home/myuser/mysite/ve/lib/python2.7/site-packages/sqlalchemy/sql/default_comparator.py", line 166, in _unsupported_impl
"this expression" % op.__name__)
When I delete case expression (leaving just coalesce), it works fine. But I can't get Core to accept case.
How to make it work with case?
Well it turned out to be the case of "RTFM carefully":
case(
[
(coalesce(base_vee.c.affidavit, '') == '', False)
],
else_=True).label('emptyish')
])