Re: SQL server 7 equivalent code in Oracle?

From: Norris <jcheong_at_cooper.com.hk>
Date: 2000/06/13
Message-ID: <8i4s16$2hif$1@adenine.netfront.net>#1/1

How can I decode the following query?

SELECT
CASE
WHEN price IS NULL THEN 'Not yet priced' WHEN price < 10 THEN 'Very Reasonable Title' WHEN price >= 10 and price < 200 THEN 'Coffee Table Title' WHEN price >= 200 and price < 211 THEN 'Tea Table Title' ELSE 'Expensive book!'
END
FROM titles

In comp.databases.oracle.server Eugenio <eugenio.spadafora_nospam_at_compaq.com> wrote:
> 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

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

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