Jaspersoft Studio中ORDER BY用CASE WHEN报错：No viable alternative at input 'case'

你碰到的"No viable alternative at input 'case'"错误，主要是SQL语法问题加上逻辑冗余导致的，具体问题和修复方案如下：

核心问题 #

1. 语法错误：原SQL中两个CASE排序块之间没有加逗号，SQL要求多个排序条件必须用逗号分隔，这是直接触发报错的原因
2. 逻辑冗余：没必要拆成两个CASE分别处理升序降序，完全可以合并成一个逻辑，通过参数直接指定排序方向
3. 字段错误：Expiration date对应的字段写错了，你用了IRB_EXPIRATION_NA（这是“是否过期不适用”的标识字段），实际应该用IRB_EXPIRATION日期字段
4. CASE分支不完整：没有设置ELSE，如果参数匹配失败会返回NULL，可能导致排序异常

修正后的SQL代码 #

select "RV_PROTOCOL_BASIC"."PROTOCOL_NO" as protocol_no,
        "RV_PROTOCOL_BASIC"."PI_NAMES" as principal_investigator,
        "RV_PROTOCOL_BASIC"."STATUS" as current_status,
        "RV_PROTOCOL_BASIC"."SPONSOR" as sponsor,
        CAST("RV_PROTOCOL_BASIC"."IRB_EXPIRATION" as DATE) as expiration_date,
        "RV_PROTOCOL_BASIC"."IRB_EXPIRATION_NA" as expiration_na
from "RV_PROTOCOL_BASIC"
join "RV_PROTOCOL_DETAILS"
    on "RV_PROTOCOL_BASIC"."PROTOCOL_NO" = "RV_PROTOCOL_DETAILS"."PROTOCOL_NO"
where (
        "RV_PROTOCOL_BASIC"."STATUS" = 'OPEN TO ACCRUAL'
        OR "RV_PROTOCOL_BASIC"."STATUS" = 'CLOSED TO ACCRUAL'
        OR "RV_PROTOCOL_BASIC"."STATUS" = 'SUSPENDED'
    )
    AND (
        "RV_PROTOCOL_BASIC"."IRB_EXPIRATION" is null
        or "RV_PROTOCOL_BASIC"."IRB_EXPIRATION" < $P{currentDate}
        or "RV_PROTOCOL_BASIC"."IRB_EXPIRATION_NA" = 'Y'
    )
    AND $X{IN, "RV_PROTOCOL_BASIC"."PI_NAMES", selectedPI}
    AND $X{IN, "RV_PROTOCOL_BASIC"."SPONSOR", selectedSponsor}
    AND $X{IN, "RV_PROTOCOL_DETAILS"."DT4_REPORT_TYPE", dt4Type}
order by
    case $P!{sortBy}
        when 'Protocol no.' then "RV_PROTOCOL_BASIC"."PROTOCOL_NO"
        when 'Pis' then "RV_PROTOCOL_BASIC"."PI_NAMES"
        when 'Current status' then "RV_PROTOCOL_BASIC"."STATUS"
        when 'Sponsor' then "RV_PROTOCOL_BASIC"."SPONSOR"
        when 'Expiration date' then "RV_PROTOCOL_BASIC"."IRB_EXPIRATION"
        when 'Expiration n/a?' then "RV_PROTOCOL_BASIC"."IRB_EXPIRATION_NA"
        ELSE "" -- 加ELSE避免NULL
    end $P!{sortOrder}

修复说明 #

• 合并了两个CASE块，通过$P!{sortOrder}直接拼接ASC或DESC，简化逻辑同时避免语法错误
• 修正了Expiration date对应的字段，确保按实际过期日期排序
• 添加了ELSE ""分支，防止参数不匹配时返回NULL影响排序结果
• 去掉了重复的条件判断，让SQL更简洁易维护

内容的提问来源于stack exchange，提问作者jshaub