Home » SQL & PL/SQL » SQL & PL/SQL » Query Help
Query Help [message #185960] Fri, 04 August 2006 09:15 Go to next message
cajohn
Messages: 10
Registered: July 2006
Junior Member
I am sorry if I post this in the wrong section, but I need some help with a query.

I have a query that runs and basically gets a Top 10 for a weeks timeframe by a certain device number. The problem is that I have 4 different devices that I need to run this query for. It takes on average 2+ hours for each query to run. I was wondering if I could get this into one query.

Here is the query:
select EVT_NM, OUT_TOT,IN_TOT, TOT from
( select distinct substr(d1.name,1,40) EVT_NM,
nvl(sum(case when d1.zone is NULL then d1.e_count end),0) OUT_TOT,
nvl(sum(case when d1.zone is NOT NULL then d1.e_count end),0) IN_TOT,
sum(d1.e_count) TOT,
rank() over(order by sum(d1.e_count) DESC) rank
from data d1
where (d1.e_time between to_timestamp(s_date,'YYYY-MM-DD HH24:MI:SS.FF3')
and to_timestamp(e_date,'YYYY-MM-DD HH24:MI:SS.FF3'))
and d1.d_custom_number = 0 (This is where I say what four devices to do: 0,1,2,3)
and d1.e_type != 2
group by d1.name
order by TOT DESC )
where rank <= 10;
Re: Query Help [message #186003 is a reply to message #185960] Fri, 04 August 2006 13:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
select EVT_NM, OUT_TOT,IN_TOT, TOT from
( select distinct substr(d1.name,1,40) EVT_NM,
nvl(sum(case when d1.zone is NULL then d1.e_count end),0) OUT_TOT,
nvl(sum(case when d1.zone is NOT NULL then d1.e_count end),0) IN_TOT,
sum(d1.e_count) TOT,
rank() over(PARTITION BY d1.d_customer_number order by sum(d1.e_count) DESC) rank
from data d1
where (d1.e_time between to_timestamp(s_date,'YYYY-MM-DD HH24:MI:SS.FF3')
and to_timestamp(e_date,'YYYY-MM-DD HH24:MI:SS.FF3'))
and d1.d_custom_number IN (0,1,2,3)
and d1.e_type != 2
group by d1.name
order by TOT DESC )
where rank <= 10;
Re: Query Help [message #186350 is a reply to message #185960] Mon, 07 August 2006 10:53 Go to previous message
cajohn
Messages: 10
Registered: July 2006
Junior Member
I thank you very much for your solution. It works great. I was able to run this one query in a little over 2 hours, instead of running 4 different ones that took at least 10 hours. You have taught me something new that I will use in the future.

Thanks.
Previous Topic: How Many columns can we create in oracle 9i table?
Next Topic: Your help is needed.
Goto Forum:
  


Current Time: Thu Dec 08 00:30:52 CST 2016

Total time taken to generate the page: 0.19456 seconds