This forum is currently locked. You can't register or post questions at this time. (read more)

Clashing of Records

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Clashing of Records

Postby ponic » Tue Aug 07, 2012 5:29 am

In have a table called PRODUCTS, table structure and records can be found here.

PRODUCT_NO_REGISTRATION_NO is allowed to be used until that is returned back. i.e. There should not be any PRODUCT_NO_REGISTRATION_NO clashing or overlapping with other PRODUCT_NO. We return the products and return date is entered in ACTUAL_RETURN_DATE and if ACTUAL_RETURN_DATE is null we take END_DATE as return date.

If user wants to extend PRODUCTS, they extend the END_DATE by adding another record with PRODUCT_EXTENSION_NO, so a product can be used till the extension ends. By doing so PRODUCT_NO remains the same along with PRODUCT_NO_REGISTRATION_NO, only extension will be in sequence like 0,1,2 . For extension records START_DATE remains the same along with PRODUCT_NO and PRODUCT_NO_REGISTRATION_NO and extension will be in sequence like 0,1,2.

How can I find out whether records are clashing or overlapping except for extension records?

E.g. if you see the records PRODUCT_NO - ORP76 with PRODUCT_EXTENSION_NO - 1 is clashing with PRODUCT_NO- ORP100 with PRODUCT_EXTENSION_NO - 0.

Any help is highly appreciable.

P.S. Database is Oracle 10g on Solaris

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Clashing of Records

Postby Tim... » Tue Aug 07, 2012 9:23 am

Hi.

Sorry. I've read your post twice and I don't understand your question. I'm sure it makes sense to you, but coming in cold like me it means nothing.

Break the issue down, showing me examples of what represents clashed data and what is OK. Maybe then I can get a handle on what you are asking.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Clashing of Records

Postby Tim... » Tue Aug 07, 2012 11:06 am

Hi.

If your question were merely about date range clashes, you could use an approach like this to check for them,

Code: Select all

select *
from   products
where  product_no = p_product_no
and    product_no_registration_no = p_product_no_registration_no
and    start_date <= p_end_date
and    end_date >= p_start_date
order by start_date


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Clashing of Records

Postby ponic » Tue Aug 07, 2012 11:25 am

First of all for sorry for not explaining properly.

Break the issue down, showing me examples of what represents clashed data and what is OK. Maybe then I can get a handle on what you are asking.


Examples of clashed records

PRODUCT_NO=ORP76 and PRODUCT_EXTENSION_NO=1 is clashing with PRODUCT_NO=ORP100 and PRODUCT_EXTENSION_NO=0 because
for PRODUCT_NO=ORP76 Actual_return_date is 04-JUL-2002 and for PRODUCT_NO=ORP100 with PRODUCT_EXTENSION_NO=0 is allotted
from 01-JUL-2002. So it is overlapping or clashing from 01-JUL-2002 to 04-JUL-2002.
Allotment should have been only after 04-JUL-2002 ,say 05-JUL-2002.

Please let me know if I am not fully able to make you understand what I am looking for.

Thanks Tim

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Clashing of Records

Postby Tim... » Tue Aug 07, 2012 11:49 am

Hi.

1) You can't write comparisons of data as prose. Display them as SQL*Plus output. Like, these two rows clash:

Code: Select all

col1  col1  col3
----  ----  ----
1..   ...   ...
2..   ...   ...


I should be able to clearly read the formatted text so I can see the comparison. Trying to read sentences that include data is a pain.

2) You have not said if the date range clash I sent is relevant or not.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Clashing of Records

Postby ponic » Tue Aug 07, 2012 12:08 pm

Query

Code: Select all

select * from products
where PRODUCT_NO='ORP76' and PRODUCT_NO_REGISTRATION_NO ='MC2090'
and PRODUCT_EXTENSION_NO = 1
union
select * from products
where PRODUCT_NO='ORP100' and PRODUCT_NO_REGISTRATION_NO ='MC2090'
and PRODUCT_EXTENSION_NO = 0


Tim your earlier reply did not suit as it we need pass parameters, I guess p_product_no you mentioned is parameter.

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Clashing of Records

Postby Tim... » Tue Aug 07, 2012 12:51 pm

Hi.

But you can adapt this method to test the rows in a query...

Code: Select all

select * from products p1
where  exists (select 1
               from   products p2
               where  p1.product_no_registration_no = p2.product_no_registration_no
               and    p1.product_extension_no > p2.product_extension_no
               and    p1.start_date <= p2.end_date
               and    p1.end_date >= p2.start_date)


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Clashing of Records

Postby ponic » Tue Aug 07, 2012 1:01 pm

Tim

The query is partially correct except for one thing.

I would like to eliminate those records which are basically extensions i.e. for extension records START_DATE remains the same along with PRODUCT_NO and PRODUCT_NO_REGISTRATION_NO and extension will be in sequence like 0,1,2.

In your query PRODUCT_NO ORP53 is basically is an extension.

Code: Select all

select * from products
where PRODUCT_NO='ORP53'
order by PRODUCT_EXTENSION_NO
, Product's end_date is basically extended.

I would like to have only those records which are clashing and it should not be extended records.

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Clashing of Records

Postby Tim... » Wed Aug 08, 2012 5:50 pm

Hi.

I'm not sure I understand. From what it sounds like, you just have to limit the query to only deal with rows where the "product_extension_no='0'"

Code: Select all

select * from products p1
where  p1.product_extension_no = '0'
and    exists (select 1
               from   products p2
               where  p2.product_extension_no = '0'
               and    p1.product_no_registration_no = p2.product_no_registration_no
               and    p1.product_extension_no > p2.product_extension_no
               and    p1.start_date <= p2.end_date
               and    p1.end_date >= p2.start_date)


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 7 guests

cron