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

Answer 1

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!