Re: SQL server 7 equivalent code in Oracle?

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

What is aux table? Can you use the following statement as example?

select title_id

, case when title_id between 'A' and 'MZ' then 'A - M'
       when title_id between 'N' and 'SZ' then 'N - S'
                                          else 'T - Z'

end
from titles

In comp.databases.oracle.server Eugenio <eugenio.spadafora_nospam_at_compaq.com> wrote:

> 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  

> =======================================================

> 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

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

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