| 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?
select title_id,
decode(greatest(title_id, 'N'),
'N', 'A - M',
decode(greatest(title_id, 'T'),
'T', 'N - S',
'T - Z'
)
)
aux table means auxiliary table in which you can put ranges and descriptive field and use it in join with your query.
-- be happy Eugenio remove _nospam from reply address Opinions are mine and do not necessarily reflect those of my company ======================================================= Norris wrote in message <8i54v6$2m64$2_at_adenine.netfront.net>...Received on Tue Jun 13 2000 - 00:00:00 CDT
>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
 |
 |