Solved

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

Posted on 2013-06-05
9
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 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 13

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 48

Accepted Solution

by:
PortletPaul earned 300 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 13

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 13

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 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