| 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?
I'm not sure, but I think that decode should be faster than function.
I tried on my test server (not fine tuned, obviously) with this 2 scripts
Script1
select sysdate from dual
/
select max(f(price)) from t
/
select sysdate from dual
/
/* f is the function
create or replace function f(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';
elsif v_price < 200 then
v_eval := 'Coffee Table Title';
elsif v_price < 211 then
v_eval := 'Tea Table Title';
else
v_eval := 'expensive book';
end if;
exception
when others then null;
Script2
select sysdate from dual
/
select
max(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!'
)
)
)
)
on a 390000 rows table t(price number)
Script1 taked 22 seconds
Script2 taked 6 seconds
-- be happy Eugenio remove _nospam from reply address Opinions are mine and do not necessarily reflect those of my company ======================================================= Norris wrote in message <8i521r$2klr$1_at_adenine.netfront.net>... >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.hkReceived on Tue Jun 13 2000 - 00:00:00 CDT
 |
 |