Yes, you can do it !
--
be happy
Eugenio
remove _nospam from reply address
Opinions are mine and do not necessarily reflect those of my company
=======================================================
Norris wrote in message <8i6odk$qn3$2_at_adenine.netfront.net>...
>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>...
>>>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
>
>
>
>
>--
>http://www.cooper.com.hk
Received on Wed Jun 14 2000 - 00:00:00 CDT
