Re: SQL server 7 equivalent code in Oracle?

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

Thanks for your suggestion. But what should I do if there is negative value in price column?

In comp.databases.oracle.server Eugenio <eugenio.spadafora_nospam_at_compaq.com> wrote:
> Probably it could be found an easier way !!
> The way is trunc division by higher edge of the range and compare with 0,
> otherwise...other range
> (obviously ordered by value ascending)
 

> select
> decode(price,
> null, 'Not yet priced',
> decode(trunc(price/10),
> 0, 'Very Reasonable Title',
> decode(trunc(price/200),
> 0, 'Coffee Table Title',
> decode(trunc(price/211),
> 0, 'Tea Table Title',
> 'Expensive book!'
> )
> )
> )
> )
> from titles

> --
> be happy
 

> Eugenio
> remove _nospam from reply address
 

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

> =======================================================
> Norris wrote in message <8i4s16$2hif$1_at_adenine.netfront.net>...

>>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

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

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