Home » SQL & PL/SQL » SQL & PL/SQL » Array Searching Logic (Oracle 9i)
Array Searching Logic [message #330587] Mon, 30 June 2008 09:27 Go to next message
deepayan
Messages: 51
Registered: December 2005
Member
Hi All ,

I got stuck in implementing an array searching Logic. Any ideas will be highly appreciated.
I have an array of records.
The declaration would be like :
   TYPE SEGMENT_RANGE IS RECORD          
                              ( ROW_NUMBER            NUMBER,
                                ACCOUNT_SEG3_FROM     NUMBER,
                                ACCOUNT_SEG3_TO       NUMBER,
                                COMPANY_SEG1_FROM     NUMBER,
                                COMPANY_SEG1_TO       NUMBER,
                                SITE_SEG2_FROM        NUMBER,
                                SITE_SEG2_TO          NUMBER,                                
                                );
                                
    TYPE SEGMENT_RANGE_ARR IS TABLE OF SEGMENT_RANGE;
    


The data in the array will be as below(For Example)

The first line itself does not include in the data. Its for a better understanding purpose.
  ROW_NUMBER  ACCOUNT_SEG3_FROM ACCOUNT_SEG3_TO   COMPANY_SEG1_FROM  COMPANY_SEG1_TO SITE_SEG2_FROM    SITE_SEG2_TO          
  1	     10000	        10050	             400	     450	       100	      102
  2	     10020	        10030	             410	     430	       100	      102
  3	     10020	        10030	             250	     290	       100	      102
  4	     10000  	        10070				
  5	     10100	        10199	             400	     450	       100	      102

 
 


Now , I need to do a check on this array of records to check the rows having overlapping ranges with row_number = 1 .
The logic for finding overlapping rows is provided below:
There are three segments : Account , Company , Site. Every segment has a range which is defined as
ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO
COMPANY_SEG1_FROM & COMPANY_SEG1_TO
SITE_SEG2_FROM & SITE_SEG2_TO


Row1 is having conflicted with Row2 because ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO of Row1 falls between ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO of Row2 and same for the other two segments .

Row3 is having no conflict with Row1 because even though ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO of Row3 is falling within the range of ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO of Row1 but COMPANY_SEG1_FROM & COMPANY_SEG1_TO for both rows is not within the range .

Row1 & Row4 are in conflict because ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO are within the range but COMPANY_SEG1_FROM & COMPANY_SEG1_TO is blank. That means all possible ranges are allowed. So it’s in a conflict.

Row1 & Row5 is not having conflict because Account Segment is not in range , though company segment is in range.

So , to make a summary , If all the segments ranges are in overlapping, then only there is a conflict.
Otherwise its OK.

So , the outcome will be Row2 & Row4 are in conflict with Row1.

Please advise.

Thanks in advance,
Deepayan

Re: Array Searching Logic [message #330822 is a reply to message #330587] Tue, 01 July 2008 07:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've been having a look at this, and I think your test data may be wrong.

You say that rows 1 & 4 should be in conflict because ACCOUNT_SEG3_FROM & ACCOUNT_SEG3_TO on row 4 are within the range of row 1.
But, the Account_Seg3_To value on row 4 is outside the range of row 1.

You're going to have to explain whay you mean by 'within the range' I think.
Re: Array Searching Logic [message #331046 is a reply to message #330587] Wed, 02 July 2008 03:54 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
THANKS FOR YOUR REPLY.

Let me explain what I mean:


      Account_Seg3_From  Account_Seg3_To
Row1  10000	         10050

Row3  10000  	         10070




All possible numbers between Account_Seg3_From to Account_Seg3_To of Row1 is also a part of all possible numbers between Account_Seg3_From to Account_Seg3_To for Row3.
That means the range of numbers between Account_Seg3_From to
Account_Seg3_To are not mutually exclusive.
So there is a conflict.

Let me know if you still need more clarification.

Thanks,
Deepayan


Re: Array Searching Logic [message #331073 is a reply to message #331046] Wed, 02 July 2008 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Working on the understanding that two ranges are in conflict if either one is a subset of the other, this should do the job:
drop table temp_0034;

create table temp_0034 (ROW_NUMBER         number
                       ,ACCOUNT_SEG3_FROM  number
                       ,ACCOUNT_SEG3_TO    number
                       ,COMPANY_SEG1_FROM  number
                       ,COMPANY_SEG1_TO    number
                       ,SITE_SEG2_FROM     number
                       ,SITE_SEG2_TO       number);

insert into temp_0034 values(  1,     10000,        10050,	             400,	     450,	       100,	      102);
insert into temp_0034 values(  2,     10020,        10030,	             410,	     430,	       100,	      102);
insert into temp_0034 values(  3,     10020,        10030,	             250,	     290,	       100,	      102);
insert into temp_0034 values(  4,     10000,        10070,		null	,           null    ,	      null    ,    null  );
insert into temp_0034 values(  5,     10100,        10199,	             400,	     450,	       100,	      102);


    
select t1.row_number
      ,t2.row_number
from   temp_0034 t1,
       temp_0034 t2
where  t1.row_number =1
and    t2.row_number !=1
and   ((t2.account_seg3_from between t1.account_seg3_from and t1.account_seg3_to
    and t2.account_seg3_to   between t1.account_seg3_from and t1.account_seg3_to)
    or (t1.account_seg3_from between t2.account_seg3_from and t2.account_seg3_to
    and t1.account_seg3_to   between t2.account_seg3_from and t2.account_seg3_to))
and  (( nvl(t2.company_seg1_from,t1.company_seg1_from) between t1.company_seg1_from and t1.company_seg1_to
    and nvl(t2.company_seg1_to  ,t1.company_seg1_from) between t1.company_seg1_from and t1.company_seg1_to)
    or (t1.company_seg1_from between t2.company_seg1_from and t2.company_seg1_to
    and t1.company_seg1_to   between t2.company_seg1_from and t2.company_seg1_to));
Re: Array Searching Logic [message #331115 is a reply to message #330587] Wed, 02 July 2008 05:53 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Thanks a lot.
Its working exactly as I intended.

One more request :

Now , I need to find the all possible conflicts in the existing rows.
For example :
Considering the same table data :
1	10000	10050	400	450
2	10020	10030	410	430
3	10020	10030	250	290
4	10000	10070		
5	10100	10199	400	450
   


The output will be :
1	2
1	4
2       4

There is a conflict between row2 & row4 . Because the account range of Row2 is within the range of Row4.
Please note that , Row2 & Row3 have no conflict , because eventhough the account range is same but company range is not a subset of the other.

Thanks,
Deepayan
Re: Array Searching Logic [message #331125 is a reply to message #331115] Wed, 02 July 2008 05:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By your logic though, row 3 & 4 should be in conflict. The account range for row 3 is a subset of that for row 4, and row 4 has blank company ranges.

Try this:
select distinct t1.row_number
      ,t2.row_number
from   temp_0034 t1,
       temp_0034 t2
where  t1.row_number != t2.row_number
and   ((t2.account_seg3_from between t1.account_seg3_from and ...
Re: Array Searching Logic [message #331131 is a reply to message #330587] Wed, 02 July 2008 06:00 Go to previous message
deepayan
Messages: 51
Registered: December 2005
Member
yes .Exactly.3 & 4 are also in conflict.
I will try your sloution.

Thanks once again.

Deepayan
Previous Topic: Any method for deleting record data ?
Next Topic: doubt in a group by query
Goto Forum:
  


Current Time: Fri Dec 02 22:56:41 CST 2016

Total time taken to generate the page: 0.14740 seconds