I am attempting to create a custom report for my organizations OnCore environment, using Jaspersoft Studio 6.20.0. The SQL query I've written includes an ORDER BY CASE WHEN clause. An error is thrown on the CASE keyword, stating "no viable alternative at input 'case'". How can I fix this error? 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 when $P!{sortBy} = 'Protocol no.' and $P!{sortOrder} = 'Ascending' then protocol_no ASC when $P!{sortBy} = 'Pis' and $P!{sortOrder} = 'Ascending' then principal_investigator ASC when $P!{sortBy} = 'Current status' and $P!{sortOrder} = 'Ascending' then current_status ASC when $P!{sortBy} = 'Sponsor' and $P!{sortOrder} = 'Ascending' then sponsor ASC when $P!{sortBy} = 'Expiration date' and $P!{sortOrder} = 'Ascending' then expiration_date ASC when $P!{sortBy} = 'Expiration n/a?' and $P!{sortOrder} = 'Ascending' then expiration_na ASC when $P!{sortBy} = 'Protocol no.' and $P!{sortOrder} = 'Descending' then protocol_no DESC when $P!{sortBy} = 'Pis' and $P!{sortOrder} = 'Descending' then principal_investigator DESC when $P!{sortBy} = 'Current status' and $P!{sortOrder} = 'Descending' then current_status DESC when $P!{sortBy} = 'Sponsor' and $P!{sortOrder} = 'Descending' then sponsor DESC when $P!{sortBy} = 'Expiration date' and $P!{sortOrder} = 'Descending' then expiration_date DESC when $P!{sortBy} = 'Expiration n/a?' and $P!{sortOrder} = 'Descending' then expiration_na DESC end[/code]
