Re: SQL server 7 equivalent code in Oracle?
Date: 2000/06/13
Message-ID: <8i4vsk$2jo2$1_at_adenine.netfront.net>#1/1
[Quoted] [Quoted] Thanks for your suggestion. But what should I do if there is negative value in price column?
[Quoted] 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>...
[Quoted] [Quoted] >>How can I decode the following query? >> >>SELECT [Quoted] >>CASE [Quoted] >>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: [Quoted] >>> This should work for SP.
>>> SELECT d.departmentid, [Quoted] [Quoted] >>> decode(trunc(p.departmentID/100), [Quoted] [Quoted] >>> 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
>>> ======================================================= [Quoted] >>> Norris wrote in message <8i49j4$22gl$1_at_adenine.netfront.net>... >>>>Which version of Oracle are you using? >>>>
[Quoted] [Quoted] >>>>In comp.databases.oracle.server Mary <mdellamalva_at_cetec.com> wrote: >>>>> Hello SP,
[Quoted] >>>>> You want to use DECODE:
[Quoted] [Quoted] >>>>> SELECT d.departmentid,
>>>>>
>>>
dept=DECODE(p.departmentID,p.departmentID<100,'Video',DECODE(p.departmentID,
[Quoted] >>>>> p.departmentID<200 [Quoted] >>>>> ,'CD-ROM',DECODE(p.departmentID,p.departmentID<300,'Book','DVD'))) >>>>> from product p, department d
[Quoted] >>>>> In a Nutshell it is: decode(<CASE>,<WHEN>,<THEN>,<ELSE>)
>>>>> Hope this helps
>>>>> Mary
>>>>> mdellamalva_at_cetec.com
[Quoted] [Quoted] >>>>> 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
[Quoted] >>>>>> 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.hkReceived on Tue Jun 13 2000 - 00:00:00 CEST