Search code examples
mysqlsqljasper-reportscase-when

How to add Multiple Variables in THEN clause When using WHEN THEN in Select statement

I am trying to use IN statement in where clause, I have written the following statement, and its giving me missing right parenthesis error message. I have google it but unable to find related solutions.

The problem is in the following statement, it works fine when i use single arguments, but it gives me error message when i use multiple arguments in the THEN clause.

I am using iReport

Kindly guide me how to use multiple arguments in THENclause

select ...
from ..

WHERE
STATUS IN
CASE
     WHEN  $P{status} is not null THEN ($P{status}, ' ')
     WHEN  $P{status} is null THEN ('V', 'R')
END
and ..
Solution

  • Change your logic to simple boolean comparisons:

    WHERE ( $P{status} is not null AND STATUS IN ($P{status}, ' ') ) OR
      ( $P{status} is null AND STATUS IN ('V', 'R') )