PL/SQL Using CASE in WHERE clause

Question

Good day Stackoverflow!

I have a query that is giving me an error: "Missing Right Parenthesis", at least, so says SQL Developer.

My query has a CASE statement within the WHERE clause that takes a parameter, and then executing a condition based on the value entered.

I've read that when using a CASE statement within a WHERE clause you have to surround the statement with parenthesis and assign it to a numeric value, e.g. "1", however doing so does not accomplish my goal.

My goal is to execute a condition in the CASE statement if that condition is met.

Would you mind taking a look and giving me some input please?

Thanks!

 SELECT ...
    FROM ....
    WHERE 1 = 1
    AND (
           CASE :P_REPORT_PERIOD
                WHEN 'SPRING' 
                  THEN ((fiscal_year = (EXTRACT(YEAR FROM (SYSDATE))-1) AND period >=10) OR (fiscal_year = (EXTRACT(YEAR FROM (SYSDATE))) AND period < 4))
                WHEN 'FALL'   
                  THEN ((fiscal_year = (EXTRACT(YEAR FROM (SYSDATE))) AND period >=4) OR (fiscal_year = (EXTRACT(YEAR FROM (SYSDATE))) AND period < 10))
           END
    ) = 1

score 1 · Answer 1 · answered Jun 08 '16 at 19:11

Problem Solved, THANKS to all those that attempted finding a solution.

Solution: Rather than using a CASE statement, I just created a stored procedure, replaced the CASE with IF and built a VSQL string from my query.

Example:

VSQL := 'SELECT.....'

 IF (v_rpt_pd = 'SPRING') THEN
    VSQL := VSQL || '( ( AND EXTRACT(YEAR FROM (SYSDATE))-1  = fiscal_year and period >=10) or ';
    VSQL := VSQL || '  ( AND EXTRACT(YEAR FROM (SYSDATE))  = fiscal_year and period <=3) )';
ELSE
    VSQL := VSQL || '( ( AND EXTRACT(YEAR FROM (SYSDATE))  = fiscal_year and period >=4) or ';
    VSQL := VSQL || '  ( AND EXTRACT(YEAR FROM (SYSDATE))  = fiscal_year and period <=9) )';
 END IF; 

VSQL := VSQL ||' GROUP BY fiscal_year, period

and so on, if you want the entire solution, DM me and I'll send you the code.

Cheers!

mahi_0707 · Answer 2 · 2016-06-08T20:33:44.400

As per Tom the CASE syntax in WHERE CLAUSE is -

 --Syntax
    select *  from <table name>
    where 1=1
    and 
    (case 
    when <BOOLEAN_EXPRESSION> 
    then <SCALAR_RETURN_VALUE>
    ... 
    ELSE <SCALAR_RETURN_VALUE>
    end) = <SCALAR_VALUE> ;

Example:

 --Query       

 WITH SAMPLE_DATA AS
    (select 100 COL1,999 COL2 from DUAL UNION ALL
     select 200 COL1,888 COL2 from DUAL
     )
 SELECT * FROM SAMPLE_DATA
 WHERE 1=1 
 AND     (
          CASE COL2
            WHEN 999 THEN 1
            ELSE 0
            END 
         ) = 1 ; 

  -- Output:              
    100   999

PL/SQL Using CASE in WHERE clause

2 Answers2