Re: SQL server 7 equivalent code in Oracle?

From: Klaus Zeuch <Klaus.Zeuch_at_erls04.siemens.de>
Date: 2000/06/13
Message-ID: <8i52jb$18s$1@papyrus.erlm.siemens.de>#1/1

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
Received on Tue Jun 13 2000 - 00:00:00 CDT