Re: SQL server 7 equivalent code in Oracle?

From: Eugenio <eugenio.spadafora_nospam_at_compaq.com>
Date: 2000/06/13
Message-ID: <8i4oak$10$1@mailint03.im.hou.compaq.com>#1/1

This should work for SP.

SELECT d.departmentid,
decode(trunc(p.departmentID/100),

                      0, 'Video',
                      1, 'CD-ROM',
                      2, 'Book',
                          'DVD')  as dept

from product p, department d
where d.prodID = p.prodID
order by dept desc

--
be happy

Eugenio
remove _nospam from reply address

Opinions are mine and do not necessarily reflect those of my company

=======================================================
Norris wrote in message <8i49j4$22gl$1_at_adenine.netfront.net>...


>Which version of Oracle are you using?

>

>In comp.databases.oracle.server Mary <mdellamalva_at_cetec.com> wrote:

>> Hello SP,

 


>> You want to use DECODE:

 


>> SELECT d.departmentid,

 


>>

 dept=DECODE(p.departmentID,p.departmentID<100,'Video',DECODE(p.departmentID,


>> p.departmentID<200

>> ,'CD-ROM',DECODE(p.departmentID,p.departmentID<300,'Book','DVD')))

>> from product p, department d

 


>> In a Nutshell it is:  decode(<CASE>,<WHEN>,<THEN>,<ELSE>)

 


>> Hope this helps

 


>> Mary

>> mdellamalva_at_cetec.com

 


>> SP <spant1_at_aol.com> wrote in message

>> news:MVe15.1169$Qf6.46762_at_nuq-read.news.verio.net...

>>> I've this SQL code inside a PL/SQL body.  But this is in SQL server 7.

 Can


>>> anyone suggest eqivalent code in Oracle.

>>> SELECT d.departmentid,

>>>  'dept'=CASE

>>>  WHEN p.departmentID<100

>>>   THEN 'Video'

>>>  WHEN p.departmentID<200

>>>   THEN 'CD-ROM'

>>>  WHEN p.departmentID<300

>>>   THEN 'Book'

>>>  ELSE 'DVD'

>>>   END

>>> from product p, department d

>>> where d.prodID = p.prodID

>>> order by dept desc

>>>

>>> Thanks,

>>> SP

>>>

>>>

>

>

>

>

>--

>http://www.cooper.com.hk

Received on Tue Jun 13 2000 - 00:00:00 CDT