OraFAQ Forum: SQL & PL/SQL » Decode function (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Decode function (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Decode function (merged) [message #210455]

Wed, 20 December 2006 20:45

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

hello orafaq team..

i m facing problem while doing decode.
can any one plz help me to over come from it?


SQL> SELECT * FROM CRIMINAL;
     SL_NO NAME                 DEGREE          WEIGHT     CLASS
---------- -------------------- --------------- ---------- ----------
         1 ashish               matric          45kg
         2 sumant               bsc             50kg
         3 yas                  bcom            46kg
         4 rupak                bca             60kg
4 rows selected.
SQL>ed
Wrote file afiedt.buf
  1* select sl_no, name, degree, decode(sign(weight-50), -1, 'low', 0, 'normal', 1, 'good') from criminal
SQL>/
select sl_no, name, degree, decode(sign(weight-50), -1, 'low', 0, 'normal', 1, 'good') from criminal
                                        *
ERROR at line 1:
ORA-01722: invalid number

please tell what things going wrong over it, clearify me.

thanx

with regards
ishika

Report message to a moderator

Re: decode function [message #210457 is a reply to message #210455]

Wed, 20 December 2006 20:55

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Your weight column includes 'kg':

SQL> select * from criminal;

NAME                 WEIGHT
-------------------- ----------
ashish               45kg
sumant               50kg

SQL> select name, decode(sign(replace(weight,'kg')-50), -1, 'low', 0, 'normal', 1, 'good') from criminal;

NAME                 DECODE
-------------------- ------
ashish               low
sumant               normal

Report message to a moderator

Re: decode function [message #210459 is a reply to message #210455]

Wed, 20 December 2006 21:32

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

thank u very much ebrian

but i will give u my feedback,
after running it.

thanx once again
regards
ishika

Report message to a moderator

about decode [message #210460 is a reply to message #210455]

Wed, 20 December 2006 21:37

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

hello ebrian

please help me why i m not getting required result.
although query runs well but column status remains empty.
whats the problem here..
please make me understand .


select e.name,
         e.job,
         To_char(e.hiredate, 'fmdy dd-mon-yyyy')hiredate,
         case e.job when 'CLERK' then
         case when to_char(e.hiredate, 'MM') IN ('08', '03') then 'er' else 'de'
         end
         when 'MANAGER' then
         case when to_char(e.hiredate, 'yyyy') IN ('2002', '2007') then 'ij' else 'ki'
         end
         when 'ANALYST' then
         case when to_char(e.hiredate, 'yyyy') IN ('2000', '2003') then 'jh' else 'lo'
         end
         when 'SUPREME' then
         case when to_char(e.hiredate, 'yyyy') IN ('2001', '2007') then 'de' else 'ws'
         end
         end status
         from CDAC E
/
NAME             JOB      HIREDATE        ST
---------------- -------- --------------- --
Ivan Bayross     clerk    wed 12-dec-2001
Vandana Saitwal  manager  wed 12-oct-2005
Paramada Jaguste clerk    wed 12-feb-2003
Basu Navindgi    manager  mon 12-feb-2001
Ravi Sreedharan  analyst  mon 12-jun-2000
Rukmini          manager  sat 12-jun-2004
Sumant           analyst  thu 12-sep-2002
Sushil           manager  sun 2-jul-2006
Rupak            clerk    sun 1-jan-2006
Rupali           analyst  sun 25-dec-2005
Ramesh           manager  sun 29-feb-2004
Rupesh           clerk    thu 12-feb-2009
Ritika            supreme mon 31-dec-2001
Shiva            manager  fri 31-jan-1890
Ishika           supreme  tue 23-jun-1970
mamoom           supreme  wed 31-jan-1990
Poonam           manager  sat 28-aug-1976
Ashish           manager  fri 20-sep-1991
18 rows selected.

with regards
ishika

Report message to a moderator

Re: about decode [message #210462 is a reply to message #210460]

Wed, 20 December 2006 22:09

bonker
Messages: 402
Registered: July 2005

Senior Member

that is because Oracle does case sensitive comparison

select e.name,
         e.job,
         To_char(e.hiredate, 'fmdy dd-mon-yyyy')hiredate,
         case upper(trim(e.job))when 'CLERK' then
         case when to_char(e.hiredate, 'MM') IN ('08', '03') then 'er' else 'de'
         end
         when 'MANAGER' then
         case when to_char(e.hiredate, 'yyyy') IN ('2002', '2007') then 'ij' else 'ki'
         end
         when 'ANALYST' then
         case when to_char(e.hiredate, 'yyyy') IN ('2000', '2003') then 'jh' else 'lo'
         end
         when 'SUPREME' then
         case when to_char(e.hiredate, 'yyyy') IN ('2001', '2007') then 'de' else 'ws'
         end
         end status
         from CDAC E

or in oracle 10g you can do case insensitive comparison as well. Read this

http://www.orafaq.com/node/999

[Updated on: Thu, 21 December 2006 00:20]

Report message to a moderator

Previous Topic:	record type - Urgent help required
Next Topic:	sqlplpus Connect behaviour on 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 12:48:30 CST 2024