JasperReport - error with conditional parameter in SQL

[Chengwei Lee]

I'm trying to have a query that returns all rows if user_id parameter is NULL/empty. The same query should return a specific row if user_id is not NULL/empty. I did some Googling and found the following suggestion. However, when I preview the Jasper report in the iReport, I hit a Oracle missing keyword error. I checked my syntax and it seems to be okay.

Any help is appreciated!

SELECT username, remarks
FROM audit_log
WHERE status = 'F'
AND action_name        = 'login'
AND tx_timestamp between $P{START_DATE} AND $P{END_DATE}
AND (
  CASE WHEN $P{USER_ID} IS NULL THEN TRUE ELSE user_id = $P{USER_ID} END
)