CODEWITHSUNDEEP
sqloracle-databaseoracle11gcase
Elian
Elian

Reputation: 21

Missing Keyword (ORA-00905) - Oracle Case Statement

I tried to write a case statement in a select statement. Since the height column comes as varchar2 and includes characters I need to eliminate the characters as follows :

SELECT FIRST_NAME,
    LAST_NAME,
    (CASE height
        WHEN height LIKE '%>%' THEN -1
        WHEN height LIKE '%<%' THEN -1
        ELSE TO_NUMBER (height)
            END) AS height
FROM TEMP;

I am getting this Missing keyword error. I am a newbie and would be nice if anyone could help me with this.

Upvotes: 1

Views: 5805

Answers (2)

collapsar
collapsar

Reputation: 17238

You got the syntax of the searched case expression wrong:

SELECT FIRST_NAME,
       LAST_NAME,
       (CASE
          WHEN height LIKE '%>%' THEN -1
          WHEN height LIKE '%<%' THEN -1
          ELSE TO_NUMBER (height)
        END) AS height
  FROM TEMP
     ;

For the sake of clarity you may wish to merge the conditions producing the same code ( the usefulness depends on the application, of course ):

CASE WHEN height LIKE '%>%'
       OR height LIKE '%<%' 
     THEN -1
     ELSE TO_NUMBER (height)
END

A variant using regular expressions (beware of performance implications):

CASE WHEN REGEXP_LIKE ( height, '[<>]' ) 
     THEN -1
     ELSE TO_NUMBER (height)
END

Upvotes: 1

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

try this

SELECT FIRST_NAME,
       LAST_NAME,
       (CASE
         WHEN height LIKE '%>%' THEN
          -1
         WHEN height LIKE '%<%' THEN
          -1
         ELSE
          TO_NUMBER(height)
       END) AS height   
  FROM TEMP;

Upvotes: 0

Related Questions