Search code examples
sqloraclewhere-clausecase-statement

Need viable solution to my invalid where criteria creation

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)
Solution

  • 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