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).
Received on Thu May 23 2002 - 16:54:20 CDT