Error in ORDER BY CASE WHEN Clause

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
jshaub's picture
2
Joined: Aug 25 2022 - 6:44am
Last seen: 7 months 4 days ago

Have you tried enclosing your case inside a parentheses?

luked - 7 months 1 week ago

I have, and that only changes the error to "no viable alternative at input '('".

jshaub - 7 months 1 week ago

0 Answers:

No answers yet
Feedback
randomness