SqlAlchemy CASE Statement: ValueError: too many values to unpack (expected 2)

Question

Does anyone know why the following SqlAlchemy CASE statement fails with the following error message:

casePrimaryApprover = case(
    [
        (and_(
            (agreement.approving_official_id.is_not(None)),
            (approver.current_flag == 'Y'),
            (approver.inactive_date.is_(None)), 
            (approver.organizationalstat == 'EMPLOYEE'),
            or_(
                (participatingIc.ic_nihsac == func.substr(approver.nihsac, 1, 3)), 
                (externalPeoplePrimaryApprover.id.is_not(None))
            )
        )), 'Y'
    ],
    else_ = 'N'
)

Error

    casePrimaryApprover = case(
  File "<string>", line 2, in case

  File "C:\gitForVS\sm_telework\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 2844, in __init__
    whenlist = [
  File "C:\gitForVS\sm_telework\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 2844, in <listcomp>
    whenlist = [
ValueError: too many values to unpack (expected 2)

Note that this should correspond to the SQL

            CASE
                WHEN
                    agreement_t_1.approving_official_id is not null and 
                    ned_person_t_2.current_flag = 'Y' and
                    ned_person_t_2.inactive_date is null and
                    ned_person_t_2.organizationalstat = 'EMPLOYEE' and 
                    (pi.ic_nihsac = substr(ned_person_t_2.nihsac,1,3) 
                      or
                     epApprover.id is not null)
               THEN
                   'Y'
               ELSE
                   'N'
            END AS APPROVER1,

score 0 · Accepted Answer · answered Sep 15 '21 at 17:17

I fixed it. Same issue as in SqlAlchemy Case with multiple conditions `and_` more than two values?

My second-last closing parenthesis had to be before the 'Y'.

casePrimaryApprover = case(
    [
        (and_(
            (agreement.approving_official_id.is_not(None)),
            (approver.current_flag == 'Y'),
            (approver.inactive_date.is_(None)), 
            (approver.organizationalstat == 'EMPLOYEE'),
            or_(
                (participatingIc.ic_nihsac == func.substr(approver.nihsac, 1, 3)), 
                (externalPeoplePrimaryApprover.id.is_not(None))
            )
        ), 'Y')
    ],
    else_ = 'N'
)

SqlAlchemy CASE Statement: ValueError: too many values to unpack (expected 2)

1 Answers1

Linked