Re: SQL server 7 equivalent code in Oracle?

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

From the performance point of view, which is faster: user defined stored function or decode or cursor or temp table...?

In comp.databases.oracle.server Klaus Zeuch <Klaus.Zeuch_at_erls04.siemens.de> wrote:
> with a stored function like
 

> create or replace function fct_price_eval(v_price in number) return varchar2
> is
> v_eval varchar2(100) := 'error in evaluating';
> begin
> begin
> if v_price is null then
> v_eval := 'Not yet priced';
> elsif v_price < 10 then
> v_eval := 'very reasonable title';
> -- further evaluation code with elsif.....then.....;
> else
> v_eval := 'expensive book';
> end if;
> exception
> when others then null;
> end;
> return v_eval;
> end;
> /
 

> you should be able to handle every logic and can avoid lengthy
> decode-statements.
 

> SELECT price, fct_price_eval(price),...
> FROM ....
 
> regards
 

> Klaus
> Norris <jcheong_at_cooper.com.hk> schrieb in im Newsbeitrag:
> 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