Home » SQL & PL/SQL » SQL & PL/SQL » Case and Decode (9i)
Case and Decode [message #358494] Tue, 11 November 2008 10:38 Go to next message
rajput.anshu
Messages: 45
Registered: October 2008
Location: Bangalore
Member
I want few more difference between Case and Decode function....I know only one that Case expression complies with ANSI code; Decode is specific to oracle syntax.
Re: Case and Decode [message #358495 is a reply to message #358494] Tue, 11 November 2008 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DECODE is DECODE and CASE is CASE.
What do you want more?
Maybe SQL Reference

Regards
Michel
Re: Case and Decode [message #358497 is a reply to message #358495] Tue, 11 November 2008 10:51 Go to previous messageGo to next message
rajput.anshu
Messages: 45
Registered: October 2008
Location: Bangalore
Member
I seen there...But i am unable to find out.
Re: Case and Decode [message #358504 is a reply to message #358497] Tue, 11 November 2008 11:26 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rajput.anshu,

You could also use the SEARCH utility in this forum and Google you know.

This following link might give some help:
DECODE VS CASE

Hope this helps,

Regards,
Jo
Re: Case and Decode [message #358716 is a reply to message #358504] Wed, 12 November 2008 05:05 Go to previous messageGo to next message
rajput.anshu
Messages: 45
Registered: October 2008
Location: Bangalore
Member
I seen many places that we can use range values in case but not in decode..............Will anyone give me one example.....Thanks
Re: Case and Decode [message #358720 is a reply to message #358716] Wed, 12 November 2008 05:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rajput.anshu wrote on Wed, 12 November 2008 12:05
I seen many places that we can use range values in case but not in decode..............Will anyone give me one example.....Thanks

As you did not post an example of "use range values in case", I am afraid that hardly anybody will have any idea what exactly you mean by this. I could only (more or less closely) guess.
Re: Case and Decode [message #358725 is a reply to message #358494] Wed, 12 November 2008 05:28 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



It is not true exactly .

Even range values can be expressed using DECODE . But it wont be flexible and readable as expressions in CASE.

Here is one example.

SQL> SELECT EMPNO , SAL ,
  2         CASE  WHEN SAL BETWEEN 0 AND 1000 THEN 'L'
  3                  WHEN SAL BETWEEN 1001 AND 2000 THEN 'M'
  4                  WHEN SAL BETWEEN 2001 AND 5000 THEN 'H'END SAL_RANK
  5  FROM EMP;

     EMPNO        SAL S
---------- ---------- -
      7369        800 L
      7499       1600 M
      7521       1250 M
      7566       2975 H
      7654       1250 M
      7698       2850 H
      7782       2450 H
      7788       3000 H
      7839       5000 H
      7844       1500 M
      7876       1100 M
      7900        950 L
      7902       3000 H
      7934       1300 M

14 rows selected.

SQL> SELECT EMPNO , SAL ,
  2         DECODE  (LEAST (SAL,1001),SAL ,'L',
  3                  DECODE(LEAST (SAL,2001),SAL ,'M',
  4                         DECODE(LEAST (SAL,5001),SAL ,'H','H')))
  5                 SAL_RANK
  6  FROM EMP;

     EMPNO        SAL S
---------- ---------- -
      7369        800 L
      7499       1600 M
      7521       1250 M
      7566       2975 H
      7654       1250 M
      7698       2850 H
      7782       2450 H
      7788       3000 H
      7839       5000 H
      7844       1500 M
      7876       1100 M
      7900        950 L
      7902       3000 H
      7934       1300 M

14 rows selected.

SQL>


Smile
Rajuvan.
Previous Topic: Procedure
Next Topic: need an idea
Goto Forum:
  


Current Time: Sat Dec 03 04:12:51 CST 2016

Total time taken to generate the page: 0.14521 seconds