CODEWITHSUNDEEP
sqloracle-database
Daniel
Daniel

Reputation: 9

Using Case and To_Char

Could someone help me with the below? 

select  CASE ( TO_CHAR (creation_date ,'MON')
 WHEN 'JAN' THEN 1
 WHEN 'FEB' THEN 2
 WHEN 'MAR' THEN 3
 WHEN 'APR' THEN 4 
 ELSE 'NULL') AS PERIOD 
from po_line_locations_all

I am getting error message -

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis".

I am no expert so if someone could help me with the above.

Many thanks Dan

Upvotes: 0

Views: 1229

Answers (2)

vercelli
vercelli

Reputation: 4757

Tony Andrews got it right, but I wouldn't go for to_char 'MON' since it depends on you language. I'd go for extract

 select
     Case 
       when extract(MONTH from creation_date ) > 4 then NULL
       else extract(MONTH from creation_date )  end AS PERIOD
  from po_line_locations_all;

This is why it's a bad idea:

 alter session set NLS_LANGUAGE = 'ENGLISH';
 select extract(MONTH from sysdate) extr, to_char(sysdate,'MON') tochar from dual;
 alter session set NLS_LANGUAGE = 'SPANISH';
 select extract(MONTH from sysdate) extr, to_char(sysdate,'MON') tochar from dual;

OUTPUT

Session altered.
      EXTR TOCHAR
---------- ------
         8 AUG

Session altered.
      EXTR TOCHAR
---------- ------
         8 AGO

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

You need an END, and you don't need the parentheses:

select  CASE  TO_CHAR (creation_date ,'MON')
 WHEN 'JAN' THEN 1
 WHEN 'FEB' THEN 2
 WHEN 'MAR' THEN 3
 WHEN 'APR' THEN 4 
 ELSE NULL END AS PERIOD 
from po_line_locations_all;

Also NULL should not be quoted. In fact the ELSE is superfluous, as NULL is the default anyway:

select  CASE  TO_CHAR (creation_date ,'MON')
 WHEN 'JAN' THEN 1
 WHEN 'FEB' THEN 2
 WHEN 'MAR' THEN 3
 WHEN 'APR' THEN 4 
 END AS PERIOD 
from po_line_locations_all;

Upvotes: 2

Related Questions