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 <[EMAIL PROTECTED]>
> 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
> || [EMAIL PROTECTED]
> || <)))><
>
> */
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
