Re: SQL server 7 equivalent code in Oracle?

From: Norris <jcheong_at_cooper.com.hk>
Date: 2000/06/14
Message-ID: <8i6odk$qn3$2_at_adenine.netfront.net>

[Quoted] Can I apply GREATEST function to date or decimal data type?

In comp.databases.oracle.server Eugenio <eugenio.spadafora_nospam_at_compaq.com> wrote:
> select title_id,
> decode(greatest(title_id, 'N'),
> 'N', 'A - M',
> decode(greatest(title_id, 'T'),
> 'T', 'N - S',
> 'T - Z'
> )
> )
> from titles

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

[Quoted] >>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:

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

[Quoted] >>> 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: [Quoted] >>>>> 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: [Quoted] >>>>>>> This should work for SP.  

>>>>>>> SELECT d.departmentid,
[Quoted] >>>>>>> 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,  

[Quoted] >>>>>>>>> You want to use DECODE:  

>>>>>>>>> SELECT d.departmentid,  

>>>>>>>>>
>>>>>>>
>>>>>
>>>

 dept=DECODE(p.departmentID,p.departmentID<100,'Video',DECODE(p.departmentID,

[Quoted] >>>>>>>>> p.departmentID<200
>>>>>>>>> ,'CD-ROM',DECODE(p.departmentID,p.departmentID<300,'Book','DVD')))
>>>>>>>>> from product p, department d
 

[Quoted] >>>>>>>>> In a Nutshell it is: decode(<CASE>,<WHEN>,<THEN>,<ELSE>)  

>>>>>>>>> Hope this helps  

>>>>>>>>> Mary
>>>>>>>>> mdellamalva_at_cetec.com  

[Quoted] >>>>>>>>> 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

[Quoted] >>>>>>>>>> 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

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

Received on Wed Jun 14 2000 - 00:00:00 CEST