need help with PL SQL query 1

[DotNetGnat]

Guys, I have previously posted this question on the wrong and was directed here...

I need help in writing a query on the table shown below.

Table1
________

SegmentId | RouteId | LeftFrom | LeftTo | RightFrom | RightTo
______________________________________________________________
   1      |  1000   |  5001    |  5469  |  5000     |  5468   
   2      |  1000   |  5471    |  5885  |  5470     |  5886
   3      |  1000   |  5887    |  6001  |  5888     |  6000

   5      |  2000   |  391     |  489   |  390      |  488
   6      |  2000   |  491     |  [red]521[/red]   |  490      |  520
   7      |  2000   |  [red]521[/red]     |  601   |  522      |  600
   8      |  2000   |  603     |  699   |  602      |  698


   9      |  3000   |  10031   |  [red]10211[/red] |  10030    |  10210   
   10     |  3000   |  [red]10201[/red]   |  10419 |  10312    |  10418

For each distinct Route:

1. The query should find out if there are any overlapping values(either in the left or right values) in the segments as shown above for the RouteId 2000.

2. The query should find out discrepancies in the increasing left or right values. The values should increase from one segment to the next unlike as shown above for the RouteId 3000 in red.

thanks

-DNG

 

[SantaMufasa]

You also have Route 2000's 521 values in red. Does that represent a problem, as well, since the "LeftFrom" value is not an increase over the previous "LeftTo"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

yes 521 is an overlap problem...the numbers should be greater than the previous value...

I came up with a equivalent query in sql server but it is not that efficient. I guess I can use Decode function in place of CASE statement for oracle.

DECLARE    @Table1 TABLE 
(SegmentId INT PRIMARY KEY, 
 RouteId INT, 
 LeftFrom INT, LeftTo INT, 
 RightFrom INT, RightTo INT)

Insert into @Table1
SELECT    1, 100, 31, 51, 30, 50 UNION ALL
SELECT    2, 100, 53, 93, 52, 92 UNION ALL
SELECT    3, 100, 93, 105, 94, 104 UNION ALL

SELECT    4, 200, 211, 251, 210, 250 UNION ALL
SELECT    5, 200, 231, 391, 252, 390 UNION ALL

SELECT    6, 300, 193, 205, 192, 204 UNION ALL
SELECT    7, 300, 207, 271, 206, 104 

Select A.* from @Table1 A
Where Exists ( 
Select 'X' from @Table1 B
Where 
((
    (Select Case when B.LeftFrom < B.RightFrom then B.LeftFrom Else B.RightFrom End) BETWEEN
    (Select Case when A.LeftFrom < A.RightFrom then A.LeftFrom Else A.RightFrom End) AND
    (Select Case when A.LeftTo < A.RightTo then A.RightTo Else A.LeftTo End)
) OR
(    (Select Case when B.LeftTo < B.RightTo then B.RightTo End) BETWEEN
    (Select Case when A.LeftFrom < A.RightFrom then A.LeftFrom Else A.RightFrom End) AND
    (Select Case when A.LeftTo < A.RightTo then A.RightTo Else A.LeftTo End)
)
)
AND (A.RouteId = B.RouteId AND A.SegmentId != B.SegmentId)
)

do you have any other suggestions..

thanks

-DNG

 

[SantaMufasa]

DotNet,

I have modified only one value in your test data (SegmentID 2's RightFrom value = 5468) to cause a "right" overlap in addition to the two "left" overlaps in your sample data:

SQL> select * from table1;

 SEGMENTID    ROUTEID   LEFTFROM     LEFTTO  RIGHTFROM    RIGHTTO
---------- ---------- ---------- ---------- ---------- ----------
         1       1000       5001       5469       5000       5468
         2       1000       5471       5885       5468       5886
         3       1000       5887       6001       5888       6000
         5       2000        391        489        390        488
         6       2000        491        521        490        520
         7       2000        521        601        522        600
         8       2000        603        699        602        698
         9       3000      10031      10211      10030      10210
        10       3000      10201      10419      10312      10418

9 rows selected.

Since you didn't post an explicit format for your output, I produced a format that consolidates the overlaps for the two adjacent table rows onto a single output row. I also heavily simplified your query so that you can use SQL only instead of resorting to PL/SQL code:

col a heading "Segment|IDs" format a9
col b heading "Left|To/From|Overlaps" format a15
col c heading "Right|To/From|Overlaps" format a15
select x.segmentid||'/'||y.segmentID a
      ,x.routeID
      ,decode(sign(y.leftfrom-x.leftto),1,null
             ,x.leftto||'/'||y.leftfrom)b
      ,decode(sign(y.rightfrom-x.rightto),1,null
             ,x.rightto||'/'||y.rightfrom)c
  from (select * from table1) x
      ,(select * from table1) y
 where y.routeid = x.routeid
   and y.segmentid = x.segmentid+1
   and (y.leftfrom <= x.leftto or
        y.rightfrom <= x.rightto)
/

                     Left            Right
Segment              To/From         To/From
IDs          ROUTEID Overlaps        Overlaps
--------- ---------- --------------- ---------------
1/2             1000                 5468/5468
6/7             2000 521/521
9/10            3000 10211/10201

I ran this code from SQL*Plus.


If you have any questions, please post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

Dave,

Wow...thats a great help...I havent tried the query yet...but it looks like thats what I exactly want. Also thanks for the format. Looks great.

I will post back if I have any questions...

Have a star...

-DNG

 

[DotNetGnat]

Dave,

One problem with your query...you made an assumption that all the segmentId's are in sequential order and incremented by 1.

I am sorry I did not point this out earlier but thats not true...yes SegmentId is the primary key of the table but it need not be sequential for particular routeId and may not be incremented by 1.

So thats the reason for me to come up with that crappy query I wrote...

do you have any suggestions...

-DNG

 

[SantaMufasa]

Yes, DotNet, there is a slight modification that you can make to achieve your specification update. To produce a valid scenario that tests what you want, I've increased the segmentID on your last row to produce a gap between the two overlapping segments:

SQL> select * from table1;

 SEGMENTID    ROUTEID   LEFTFROM     LEFTTO  RIGHTFROM    RIGHTTO
---------- ---------- ---------- ---------- ---------- ----------
         1       1000       5001       5469       5000       5468
         2       1000       5471       5885       5468       5886
         3       1000       5887       6001       5888       6000
         5       2000        391        489        390        488
         6       2000        491        521        490        520
         7       2000        521        601        522        600
         8       2000        603        699        602        698
         9       3000      10031      10211      10030      10210
        25       3000      10201      10419      10312      10418

9 rows selected.

Here is the modified row that accommodates the change:

select x.segmentid||'/'||y.segmentID a
      ,x.routeID
      ,decode(sign(y.leftfrom-x.leftto),1,null
             ,x.leftto||'/'||y.leftfrom)b
      ,decode(sign(y.rightfrom-x.rightto),1,null
             ,x.rightto||'/'||y.rightfrom)c
  from (select * from table1) x
      ,(select * from table1) y
 where y.routeid = x.routeid
   and y.segmentid = (select min(segmentid)
                        from table1
                       where segmentid > x.segmentid)
   and (y.leftfrom <= x.leftto or
        y.rightfrom <= x.rightto)
/

                     Left            Right
Segment              To/From         To/From
IDs          ROUTEID Overlaps        Overlaps
--------- ---------- --------------- ---------------
1/2             1000                 5468/5468
6/7             2000 521/521
9/25            3000 10211/10201

Let us know if this matches your scenario.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

Dave,

does it work if have data (for the routeID 3000)as shown below:

13 3000 10031 10211 10030 10210
10 3000 10201 10419 10312 10418


next segmentId number need not be greater than the previous segmentId number...

thanks

-DNG

 

[SantaMufasa]

Sorry, DotNet, I thought that the SegmentIDs, besides being Unique IDs, also ordered the segments. If that is not the case, then can you please advise me the reliable method by which we can order segments? Once I know how to order the segments, I can change the code to match reality.

(Please post soon since I must leave shortly for a business trip.)

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

we can order the segments with less value for LeftFrom and high value for LeftTo.

Always FROM values are lower than TO values...

does that help...

thanks

-DNG

 

[SantaMufasa]

Given those specifications, I have modified the original data to depict valid proofs of concept:

SQL> select * from table1;

 SEGMENTID    ROUTEID   LEFTFROM     LEFTTO  RIGHTFROM    RIGHTTO
---------- ---------- ---------- ---------- ---------- ----------
       125       1000       5001       5469       5000       5468
        60       1000       5471       5885       5468       5886
       392       1000       5887       6001       5888       6000
        57       2000        391        489        390        488
       644       2000        491        521        490        520
        73       2000        521        601        522        600
       855       2000        603        699        602        698
        90       3000      10031      10211      10030      10210
        66       3000      10201      10419      10312      10418

9 rows selected.

Here is the newly modified code to meet the overlap specifications:

col a heading "Segment|IDs" format a9
col b heading "Left|To/From|Overlaps" format a15
col c heading "Right|To/From|Overlaps" format a15
select x.segmentid||'/'||y.segmentID a
      ,x.routeID
      ,decode(sign(y.leftfrom-x.leftto),1,null
             ,x.leftto||'/'||y.leftfrom)b
      ,decode(sign(y.rightfrom-x.rightto),1,null
             ,x.rightto||'/'||y.rightfrom)c
  from (select * from table1) x
      ,(select * from table1) y
 where y.routeid = x.routeid
   and y.LeftFrom = (select min(LeftFrom)
                        from table1
                       where LeftFrom > x.LeftFrom)
   and (y.leftfrom <= x.leftto or
        y.rightfrom <= x.rightto)
/

                     Left            Right
Segment              To/From         To/From
IDs          ROUTEID Overlaps        Overlaps
--------- ---------- --------------- ---------
125/60          1000                 5468/5468
644/73          2000 521/521
90/66           3000 10211/10201

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

AWESOME....works great....

Dave,you are the best...

thanks for your time...i really appreciate it...

-DNG