Re:Numeric comparison in DECODE statement

From: Jan Pruner <jan_at_pruner.cz>
Date: Mon, 20 May 2002 08:08:34 -0800
Message-ID: <F001.00464A14.20020520080834@fatcity.com>

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).

Received on Mon May 20 2002 - 11:08:34 CDT