Home » RDBMS Server » Performance Tuning » Alternate Query instead of querying the table twice (Oracle 11G,R2)
Alternate Query instead of querying the table twice [message #570507] |
Sun, 11 November 2012 10:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
prashanth7582
Messages: 34 Registered: October 2005 Location: Bangalore
|
Member |
|
|
The Item data for individual cycles is as below.
Item_tbl
Item Rundate Stddate Status
P1 03-Nov-12 03-Nov-12 A
P1 04-Nov-12 04-Nov-12 D
P2 04-Nov-12 03-Nov-12 A
The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.
In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12.
Based on above std date,the data is queried from another table to get all the Items.
Item P2 should not be considered in above case.
Below is the code which I have written which considers the rundate as Input parameter.
-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate
and rundate = <Inputdate>
and status = 'D')
-- Getting the Previous Active cycle for all disabled Items for Input date
select * from
(
select a.Item,a.stddate,a.status,rank() over (order by stddate desc) rank
from Item_tbl a,Itemdisabled b
where a.item=b.item
and a.rundate = a.stddate
and a.rundate < b.maxcycledate
)
where rank= 1
and status = 'A'
In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.
Is there any way to query above only once and get the required results using Lag/Lead functions etc.
Thanks for the help in advance.
[Updated on: Sun, 11 November 2012 10:42] Report message to a moderator
|
|
|
|
Re: Alternate Query instead of querying the table twice [message #570509 is a reply to message #570508] |
Sun, 11 November 2012 10:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
prashanth7582
Messages: 34 Registered: October 2005 Location: Bangalore
|
Member |
|
|
Hi Michel,
Thanks for reverting back..
Here is the Create table and insert statements.
create table Item_tbl
(
Item varchar2(10),
rundate date,
stddate date,
status varchar2(1)
);
Insert into item_tbl values('P1',to_date('03/11/2012','DD/MM/YYYY'),to_date('03/11/2012','DD/MM/YYYY'),'A');
Insert into item_tbl values('P1',to_date('04/11/2012','DD/MM/YYYY'),to_date('04/11/2012','DD/MM/YYYY'),'D');
Insert into item_tbl values('P2',to_date('04/11/2012','DD/MM/YYYY'),to_date('03/11/2012','DD/MM/YYYY'),'A');
Insert into item_tbl values('P3',to_date('04/11/2012','DD/MM/YYYY'),to_date('03/11/2012','DD/MM/YYYY'),'D');
Required Result:
Item Rundate Stddate Status
P1 03-Nov-12 03-Nov-12 A
|
|
|
|
Re: Alternate Query instead of querying the table twice [message #570698 is a reply to message #570507] |
Wed, 14 November 2012 07:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
prashanth7582
Messages: 34 Registered: October 2005 Location: Bangalore
|
Member |
|
|
Hi Kevin,
You are partially right.I need to have a Partition by Item but even Ordering by std_date is necessary as I have to get the latest Previous record based on std_date.
Here is the updated query.
-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate
and rundate = <Inputdate>
and status = 'D')
-- Getting the Previous Active cycle for all disabled Items for Input date
select * from
(
select a.Item,a.stddate,a.status,rank() over (Partition by Item order by stddate desc) rank
from Item_tbl a,Itemdisabled b
where a.item=b.item
and a.rundate = a.stddate
and a.rundate < b.maxcycledate
)
where rank= 1
and status = 'A'
|
|
|
Re: Alternate Query instead of querying the table twice [message #570701 is a reply to message #570698] |
Wed, 14 November 2012 08:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
No, you are missing the point (though I made a mistake in my description, I really should have said "throw away A rows").
--
-- assuming no multiple D rows between A rows and thus we cannot have a D row that is a prior for another D row
--
-- this calculates prior values for both D and A rows then tosses away the A rows
-- this can be way less work because
-- although it is not intuitive in that we calculate prior values we do not need
-- and we do not eliminate rows as early as possible
-- this solution requires only one pass of the data (eg. no join back)
--
-- this works because both D and A rows have the same logical where clause that can be applied to them
--
-- column naming should be intuitive
--
select item,stddate D_STDDATE,prior_stddate A_STDDATE
from (
select item,stddate,status,lag(stddate) over (partition by item order by stddate) prior_stddate
from item_tbl
where rundate = stddate
and rundate <= <inputdate>
and status in ('D','A')
)
where status = 'D'
/
I believe this would skip any intervening D rows, but it requires a minimum version of Oracle that supports the IGNORE NULLS clause.
create table kevt1
(
item number
, status varchar2(1)
, stddate date
)
/
insert into kevt1 values (1,'D',SYSDATE);
insert into kevt1 values (1,'C',sysdate-1);
insert into kevt1 values (1,'B',sysdate-2);
insert into kevt1 values (1,'A',sysdate-3);
select * from kevt1 order by item,stddate;
select *
from (
select item,status,stddate,lag(case when STATUS = 'A' then stddate else null end ignore nulls) over (partition by item order by stddate) prior_a_stdddate
from kevt1
where status in ('D','A')
)
where status = 'D'
/
Kevin
[Updated on: Wed, 14 November 2012 09:04] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:42:53 CDT 2024
|