| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL server 7 equivalent code in Oracle?
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!'
)
)
)
)
-- 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>...Received on Tue Jun 13 2000 - 00:00:00 CDT
>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
 |
 |