Re: Numeric comparison in DECODE statement

Because it is not possible to put 30 into format '9.99'. Increase your format in to_char to '99.99'. ;-)

Steven Joshua wrote:
>
> Hi, I tried this. not work well when in_value < 60:
>
> SELECT TO_CHAR( CASE WHEN 30 < 60 THEN 30 ELSE 30/60
> END , '9.99') FROM DUAL;
>
> it returns:
> TO_CH
> -----
> #####
>
> any idea why?
>
> Thanks
>
> --- Jan Pruner <jan_at_pruner.cz> wrote:
> > Don't use DECODE, but CASE
> >
> > SELECT TO_CHAR(
> > CASE WHEN in_value < 60 THEN in_value ELSE
> > in_value/60 END
> > , '9.99'
> > ) FROM DUAL;
> >
> > JP
> >
> >
> > > ____________________Reply
> > Separator____________________
> > > Author: Yexley Robert D Contr Det 1 AFRL/WSI
> > <Robert.Yexley_at_wpafb.af.mil>
> > > Date: 5/20/2002 7:08 AM
> > >
> > > I was wondering if anyone might have tried this
> > before, because I can't
> > > seem to get it to work. I'd like to be able to
> > determine which unit of
> > > measure to concatenate to a value by using a
> > decode statement in the query.
> > > I have a column in the database that stores time
> > in minutes, and I'd like
> > > to be able to show the output in minutes if the
> > value is less than 60, but
> > > in hours (such as 3.27 hours) if the value is
> > greater than 60. So far I've
> > > tried the following statement, but it seems to be
> > blowing up on the first
> > > comparison operator:
> > >
> > > SELECT decode(in_value,
> > to_char(to_number(in_value) <= to_number('60')),
> > > to_char(in_value)||' minutes',
> > to_char(to_number(in_value) >
> > > to_number('60')), to_char(in_value/60, '9.99')||'
> > hours')
> > > FROM dual
> > > /
> > >
> > > I'm selecting from dual just until I can get the
> > query working at all. Is
> > > what I'm trying to do even possible? Any help or
> > ideas would be greatly
> > > appreciated. Thanks in advance.
> > >
> > > _YEX_
> > >
> > > /*
> > >
> > > || Robert D. Yexley
> > > || Oracle Programmer/Analyst
> > > || Easylink Services Corporation
> > > || Professional Services
> > > || Contractor - Wright Research Site MIS
> > > || Det-1 AFRL/WSI Bldg. 45 Rm. 062
> > > || (937) 255-1984
> > > || robert.yexley_at_wpafb.af.mil
> > > || <)))><
> > >
> > > */
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jan Pruner
> > INET: jan_at_pruner.cz
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
> __________________________________________________
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Steven Joshua
> INET: wndyu_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
 Ing. Michal Zaschke           
   DB Administrator             
Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: zaschke_at_suas.cz
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michal Zaschke
  INET: zaschke_at_suas.cz

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).