?
Solved

Oracle: HBelp with basic query for evaluating a number pass/fail

Posted on 2013-06-05
9
Medium Priority
?
480 Views
Last Modified: 2013-06-05
Hello Experts,

I am wanting to write a query that will evaluate the time difference between the time an alarm was presented to the time a ticket was created, (I have that part).   What I am need is to to evaluate the duration to see if it went over 5 minutes, if it did then "Fail", else "Pass".

The issue is that I wanting to evaluate it in a single case statement.  The reason is because I have other evalautions to write and don't want to nest select statments yet.

Here is what I have so far.
Evaluator.txt
0
Comment
Question by:Maliki Hassani
  • 4
  • 4
9 Comments
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 800 total points
ID: 39222269
select ....,
round((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 86400) "seconds elapsed",
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')), 24)) || 'd ' ||
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24, 24)) || 'h ' ||
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60, 60)) || 'min ' ||
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60 * 60, 60)) || 'sec' "time elapsed"
from ....

Open in new window

0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222277
substitute "sysdate" with "ALARM_DATE" and "to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')" with TICKET_CREATE_DATE
0
 

Author Comment

by:Maliki Hassani
ID: 39222285
Let me give this a try.. Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Maliki Hassani
ID: 39222301
I appreciate the code, but my query gives me the minutes correctly.  I am looking to have it evalaute it the 5 minutes and produce the results as Pass or fail.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 39222342
select --....,
  round((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 86400) "seconds elapsed"
, case when round((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 86400) > (5*60) then 'Fail' else 'Pass' end min_5_test
, trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')), 24)) || 'd ' ||
  trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24, 24)) || 'h ' ||
  trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60, 60)) || 'min ' ||
  trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60 * 60, 60)) || 'sec' "time elapsed"
from dual --....

Open in new window

0
 

Author Comment

by:Maliki Hassani
ID: 39222350
Trying now, thanks
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222365
but my query gives me the minutes correctly.  I am looking to have it evalaute it the 5 minutes and produce the results as Pass or fail.

I don't get that, please explain again ;-)
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222379
ignore my last post, didn't read PortletPaul's response...
0
 

Author Closing Comment

by:Maliki Hassani
ID: 39222484
Thank you!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question