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 <[EMAIL PROTECTED]> 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
> > <[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).
>
> __
> 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: [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).
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michal Zaschke
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).
RE: Numeric comparison in DECODE statement
You can also implement your logic as SELECT DECODE(SIGN(in_value-60), -1, TO_CHAR(in_value)||' minutes', TO_CHAR(in_value/60, '9.99')||' hours') FROM dual / HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, May 20, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Robert, That won't work. Decode works like a switch statement. If condition 1 then value 1. You can't imbed logic operators therein. DickG. *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Numeric comparison in DECODE statement
Yexley Robert D Contr Det 1 AFRL/WSI wrote:
>
> 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,
check function SIGN() in the SQL Reference manual.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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).
RE: Numeric comparison in DECODE statement
I think in this case you're suppose to use sign as decode was only meant to
deal with specific values
e.g.
SELECT DECODE(SIGN(:in_value - 60),1,TO_CHAR(:in_value/60, '9.99')||'
hours',
-1,TO_CHAR(TO_CHAR(:in_value))||' minutes',
TO_CHAR(TO_CHAR(:in_value))||' minutes')
FROM dual
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 4:08 PM
To: Multiple recipients of list ORACLE-L
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: Yexley Robert D Contr Det 1 AFRL/WSI
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
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).
RE: Numeric comparison in DECODE statement
How about
select to_char(floor(my_col_in_minutes/60), '990') || ':' ||
to_char(mod(my_col_in_minutes, 60)) "HH:MI" from dual
/
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 11:08 AM
To: Multiple recipients of list ORACLE-L
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_
*2
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
*2
Numeric comparison in DECODE statement
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: Yexley Robert D Contr Det 1 AFRL/WSI
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).
