Home » RDBMS Server » Server Administration » Decode statement
Decode statement [message #371472] Wed, 25 October 2000 17:40 Go to next message
John Pay
Messages: 2
Registered: October 2000
Junior Member
Hi all,
I am trying to get an amount of time taken to complete a job. My problem is that I cannot use a comparision inside a decode statement. Any sugestions?

SELECT
decode(((resolve_time-start_time)/3600) ,<1,'>1hour',<24,'<1day','>1day') as elapsedTime
From myTable

Report message to a moderator

Re: Decode statement [message #371476 is a reply to message #371472] Thu, 26 October 2000 05:16 Go to previous messageGo to next message
Prem
Messages: 79
Registered: August 1998
Member
John,

Try this

select decode(least(greatest((resolve_time-start_time)/3600), 1), 24), 1, '1 hour', 24, 'More than 1 day', 'Less than 1 day') from YourTable;

hth

Prem :)

Report message to a moderator

Re: Decode statement [message #371478 is a reply to message #371472] Thu, 26 October 2000 12:49 Go to previous messageGo to next message
John Pay
Messages: 2
Registered: October 2000
Junior Member
select decode(least(greatest(((resolve_time-start_time)/3600), 1), 24), 1, '1 hour', 24, 'More than 1 day', 'Less than 1 day') from YourTable;

Thanks that does work great. What if I need to expand on this?
SELECT
decode (((I.resolve_time-I.start_time)/3600),<1,'<1hr',<24,'<1day',<168,'<1week',>=168,'>= 1 week','Unresolved') as Elasped_hr
FROM myTable;

Report message to a moderator

Re: Decode statement [message #371479 is a reply to message #371472] Thu, 26 October 2000 12:50 Go to previous messageGo to next message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
Try the tricky SIGN function:

CREATE TABLE duration_test (col1 NUMBER);

INSERT INTO DURATION_TEST VALUES ( 0.01);
INSERT INTO DURATION_TEST VALUES ( 1.02);
INSERT INTO DURATION_TEST VALUES ( 8.03);
INSERT INTO DURATION_TEST VALUES ( 25.2);
INSERT INTO DURATION_TEST VALUES ( 36.07);
INSERT INTO DURATION_TEST VALUES ( 50.1);
INSERT INTO DURATION_TEST VALUES ( 0.7);
INSERT INTO DURATION_TEST VALUES ( 0.8);

SELECT col1,
SUM(DECODE(SIGN (1-col1), 1,1, NULL)) lt_1hr,
SUM(DECODE(SIGN (24-col1), 1,1, NULL)) lt_24hr,
SUM(DECODE(SIGN (24-col1), -1,1, NULL)) gt_24hr
FROM duration_test
GROUP BY col1
ORDER BY col1

SELECT SUM(DECODE(SIGN (1-col1), 1,1, NULL)) lt_1hr,
SUM(DECODE(SIGN (24-col1), 1,1, NULL)) lt_24hr ,
SUM(DECODE(SIGN (24-col1), -1,1, NULL)) gt_24hr
FROM duration_test

Report message to a moderator

Re: Decode statement [message #371485 is a reply to message #371472] Fri, 27 October 2000 13:27 Go to previous message
Prem
Messages: 79
Registered: August 1998
Member
John,

Try this then

select decode(least(((resolve_time-start_time)/3600), 169), 169, 'More than or equal to a week', decode(least(((resolve_time-start_time)/3600), 25), 25, 'Less than a week', decode(least(((resolve_time-start_time)/3600), 1), 1, 'Less than a day', 'Less than a hour'))) from YourTable;

This looks like a decode overkill to me! I guess using a user defined function within the sql might save a lot of trouble to oracle :)

hth

Prem :)

Report message to a moderator

Previous Topic: Is it possible to export particular tablespace?
Next Topic: Package - REF CURSORS.. with parameters procedures
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 27 06:44:18 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.