Solved

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

Posted on 2013-06-05
9
466 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 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
 

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to Generate log or file on inserting duplicate records in mysql 6 34
selective queries 7 22
mysql joining from the same table 6 37
PL/SQL Display based on value 4 21
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now