Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to retrieve last one hour data from table ?

871594Jun 10 2013 — edited Jul 15 2013

Hi All,

We have a table DCTRMAIN which have elapse time of different services of application. i want to see the average response time of each of service in last hour.  in below table STARTDATE is the column to store  response timestamp  and TRANNAME stores service name.

desc DCTRMAIN
Name                           Null     Type                                                                                                                                                                                         
------------------------------ -------- --------------------------------------------------------------------------------------
APPID                                     VARCHAR2(16 CHAR)                                                                                                                                                                            
HOSTID                                  VARCHAR2(80 CHAR)                                                                                                                                                                            
CHANNEL_TYPE                            VARCHAR2(16 CHAR)                                                                                                                                                                            
STARTDATE                               DATE            

USERNAME                                VARCHAR2(64 CHAR)                                                                                                                                                                            
RESP_TIME                               NUMBER(7,3)                                                                                                                                                                                  
SOURCENAME                              VARCHAR2(64 CHAR)                                                                                                                                                                            
FUNCTIONNAME                            VARCHAR2(64 CHAR)                                                                                                                                                                            
TRANNAME                                VARCHAR2(64 CHAR)                                                                                                                                                                          
STATUS                                  NUMBER(10)                                                                                                                                                                                   

i am quering as below. But its giving some error.

select tranname,avg(resp_time) from DCTRMAIN  where startdate between (to_char(sysdate,'DD-MON-YY HHMMSS') and to_char(sysdate-1/24,'DD-MON-YY HHMMSS') group by tranname ;

Please correct me above query .

Thanks,

Venkat Vadlamudi.

Comments

BluShadow
Sorry, but your issue isn't clear.

The output you are getting is what I would expect from those queries. What are you wanting exactly? Perhaps show us by giving an example of the data if you have trouble explaining it in words.
damorgan
First run your intersect without a WHERE clause.
Examine the returned rows.

Anything you put in the WHERE clause filters (limits) the rows that will be returned by that portion of the query BEFORE the intersection takes place.
Sven W.
there is also a MINUS operator. Maybe you like that one more then INTERSECT? But since you didn't tell us your business case, we can only guess.
Aketi Jyuuzou

Maybe below.

select aVal
from (select a.Val as aVal,b.Val as bVal,
      max(b.Val) over() as hasNull
        from A Left join B
          on a.Val = b.Val)
 where hasNull is null
    or bVal is not null
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 12 2013
Added on Jun 10 2013
5 comments
36,883 views