Need viable solution to my invalid where criteria creation

Question

I need to select the three month names, from previous quarter, and apply those month names in my where statement. I can't get the following to work:

WHERE table.column in 
     (CASE WHEN to_char(sysdate, 'MM') in (01,02,03) THEN ('OCTOBER','NOVEMBER','DECEMBER')
     WHEN to_char(sysdate, 'MM') in (04,05,06) THEN ('JANUARY','FEBRUARY','MARCH')
     WHEN to_char(sysdate, 'MM') in (07,08,09) THEN ('APRIL','MAY','JUNE')
     WHEN to_char(sysdate,  'MM') in (10,11,12) THEN ('JULY', 'AUGUST', 'SEPTEMBER') 
     END)

Answer 1

You can't return multiple results from a case expression. But you can do:

WHERE CASE WHEN to_char(sysdate, 'MM') in ('01','02','03') THEN case when table.column in ('OCTOBER','NOVEMBER','DECEMBER') then 1 else 0 end
           WHEN to_char(sysdate, 'MM') in ('04','05','06') THEN case when table.column in('JANUARY','FEBRUARY','MARCH')  then 1 else 0 end
           WHEN to_char(sysdate, 'MM') in ('07','08','09') THEN case when table.column in('APRIL','MAY','JUNE') then 1 else 0 end
           WHEN to_char(sysdate, 'MM') in ('10','11','12') THEN case when table.column in('JULY', 'AUGUST', 'SEPTEMBER')  then 1 else 0 end
      END = 1

Note I've also explicitly converted the numbers to strings, that's because to_char will be returning a string too, you want to avoid implicit conversion.

DEMO

Answer 2

It looks like you want the months that fall in the quarter 9 months from now. If so, you can use:

where to_char(to_date(month_name,'MONTH'),'Q') =
      to_char(add_months(sysdate,9),'Q');

SQL Fiddle