3

I am getting ORA-00905: missing keyword error when trying to run the following query in Oracle:

select distinct MONTH_ID
from    DSS_MAIN.DIM_TIME a1
where   Case When TO_CHAR(SYSDATE,'MM') < 3
Then TO_CHAR(a1.MONTH_START_DATE,'YYYYMM') Between (TO_CHAR(SYSDATE,'YYYY')-1)||'03' And TO_CHAR(SYSDATE,'YYYYMM')
Else TO_CHAR(a1.MONTH_START_DATE,'YYYYMM') Between TO_CHAR(SYSDATE,'YYYY')||'03' And TO_CHAR(SYSDATE,'YYYYMM')
End;

The individual Between clauses work fine, so I am assuming there is something wrong with the Case syntax.

Any ideas?

bfavaretto
  • 71,580
  • 16
  • 111
  • 150
EvanMPW
  • 351
  • 1
  • 6
  • 15
  • 2
    The `TO_CHAR(a1.MONTH_START_DATE,'YYYYMM') Between (TO_CHAR(SYSDATE,'YYYY')-1)||'03' And TO_CHAR(SYSDATE,'YYYYMM')` in the case statement makes no sense. What are you trying to retrieve (can you explain in English)? – Gerrat Jul 27 '12 at 17:19
  • 1
    Additionally the expression `TO_CHAR(SYSDATE,'YYYY')-1` does not make any sense either. Why do you subtract 1 from a *string*? –  Jul 27 '12 at 17:20
  • @a_horse_with_no_name: Agreed...but I guess Oracle's implicit casting rules just turns this back into a number (eg. `select to_char(sysdate,'YYYY') -1 from dual;` -> 2011 – Gerrat Jul 27 '12 at 17:32
  • I'm trying to get a list of months YTD for a year that runs March through February. For example, if SYSDATE is in June 2012, it should return March 2012 - June 2012. If SYSDATE is February 2013, it should return March 2012 - February 2013 – EvanMPW Jul 27 '12 at 18:17

1 Answers1

3

Try this:

SELECT DISTINCT MONTH_ID
  FROM DSS_MAIN.DIM_TIME A1
 WHERE TO_CHAR(A1.MONTH_START_DATE, 'YYYYMM') 
            BETWEEN CASE
                        WHEN TO_CHAR(SYSDATE, 'MM') < '03' THEN
                            (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1) || '03'
                        ELSE
                            TO_CHAR(SYSDATE, 'YYYY') || '03'
                    END
                AND  TO_CHAR(SYSDATE, 'YYYYMM')

Case returns a value for further use, it will not be useful in the way you used it.

hmmftg
  • 1,274
  • 1
  • 18
  • 31