Re: SQL server 7 equivalent code in Oracle?

Following this way,
you should normalize to positive integers (add XXXX and translate the ranges)

If the check become too complex, use an aux table.

--
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 <8i4vsk$2jo2$1_at_adenine.netfront.net>...


>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