Missing Keyword (ORA-00905) - Oracle SQL Case Statement

Question

Good morning,

I was wondering if one of you could help me - should be fairly quick I'd imagine; I'm a newbie so prone to missing obvious things.

I have the below statement which is returning the aforementioned 905 error...any ideas?

Thanks in advance.

(CASE CONTACTS.TELEPHONE_NO_DAY
    WHEN CONTACTS.TELEPHONE_NO_DAY LIKE '07%'
    THEN CONTACTS.TELEPHONE_NO_DAY
    ELSE NULL
    END) TEL_DAY,
   (CASE CONTACTS.TELEPHONE_NO_EVE
    WHEN CONTACTS.TELEPHONE_NO_EVE LIKE '07%'
    THEN CONTACTS.TELEPHONE_NO_EVE
    ELSE NULL
    END) TEL_EVE

Answer 1

You're mixing up two ways of doing case. You either need:

CASE <expression>
WHEN <comparison expression> THEN <return expression>
...

or

CASE
WHEN <condition> THEN <return expression>
...

These are the 'simple' and 'searched' variants in the docs.

But as you can't use like in the first version, you need the second:

CASE
WHEN CONTACTS.TELEPHONE_NO_DAY LIKE '07%'
THEN CONTACTS.TELEPHONE_NO_DAY
ELSE NULL
END

You also don't need the brackets around the two case statements.