Re: SQL server 7 equivalent code in Oracle?

[Quoted] [Quoted] Probably it could be found an easier way !! [Quoted] [Quoted] The way is trunc division by higher edge of the range and compare with 0, otherwise...other range
(obviously ordered by value ascending)

select
[Quoted] decode(price,

[Quoted]         null, 'Not yet priced',
[Quoted] [Quoted]               decode(trunc(price/10),
[Quoted]                      0, 'Very Reasonable Title',
                         decode(trunc(price/200),
                                0, 'Coffee Table Title',
                                   decode(trunc(price/211),
                                          0, 'Tea Table Title',
                                             'Expensive book!'
                                         )
                               )
                    )
      )

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