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
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 #570508 is a reply to message #570507] Sun, 11 November 2012 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
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 messageGo to next message
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 #570603 is a reply to message #570509] Mon, 12 November 2012 12:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Have a look at partition by in the analytic expression. Maybe I am missing something but seems to me you only need to partition by the item. Try throwing away D rows after you compute the analytic.

[Updated on: Mon, 12 November 2012 12:13]

Report message to a moderator

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 messageGo to next message
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
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

Previous Topic: Tuning SELECT-WHERE-BETWEEN
Next Topic: Slow Join between dba_tab_cols and dba_types
Goto Forum:
  


Current Time: Thu Mar 28 21:05:13 CDT 2024