| Determine Missing Route_Seq Numbers [message #647343] |
Tue, 26 January 2016 19:30  |
 |
stun66
Messages: 3
Registered: November 2015
Location: Dallas
|
Junior Member |
|
|
I'm trying to determine all routes that has a missing number in the ROUTE_SEQ. For example, the query would return the ROUTE_ID=123 and ROUTE_SEQ=2 and also ROUTE_ID=456 and ROUTE_SEQ=3 from the below table. I started a query to determine the min and max but am unsure how to determine the missing ROUTE_SEQ between the min and max.
ROUTE_ID | ROUTE_SEQ
123 | 1
123 | 3
456 | 1
456 | 2
456 | 4
789 | 1
789 | 2
SELECT Min(route_seq),
Max(route_seq),
Max(route_id)
FROM route
GROUP BY route_id
|
|
|
|
| Re: Determine Missing Route_Seq Numbers [message #647345 is a reply to message #647343] |
Tue, 26 January 2016 21:02  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following determines all possible sequences between the min and max and subtracts the existing ones from that to determine the missing ones. There may be a better way, but this is the first thing that came to my mind.
SCOTT@orcl> CREATE TABLE route
2 (route_id NUMBER,
3 route_seq NUMBER)
4 /
Table created.
SCOTT@orcl> INSERT ALL
2 INTO route VALUES (123, 1 )
3 INTO route VALUES (123, 3 )
4 INTO route VALUES (456, 1 )
5 INTO route VALUES (456, 2 )
6 INTO route VALUES (456, 4 )
7 INTO route VALUES (789, 1 )
8 INTO route VALUES (789, 2 )
9 SELECT * FROM DUAL
10 /
7 rows created.
SCOTT@orcl> SELECT route_id, min_seq + COLUMN_VALUE route_seq
2 FROM (SELECT route_id, MIN (route_seq) min_seq, MAX (route_seq) max_seq
3 FROM route
4 GROUP BY route_id),
5 TABLE
6 (CAST
7 (MULTISET
8 (SELECT LEVEL - 1
9 FROM DUAL
10 CONNECT BY LEVEL <= (max_seq - min_seq + 1))
11 AS SYS.ODCINUMBERLIST))
12 MINUS
13 SELECT route_id, route_seq
14 FROM route
15 /
ROUTE_ID ROUTE_SEQ
---------- ----------
123 2
456 3
2 rows selected.
|
|
|
|
| Re: Determine Missing Route_Seq Numbers [message #647354 is a reply to message #647345] |
Wed, 27 January 2016 01:41 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Here we collect all possible route_seq and compare them with them in the the different routes:
WITH rges AS
(SELECT CAST (COLLECT(DISTINCT route_seq) AS KU$_OBJNUMSET) rs_ges
FROM route),
rs AS
(SELECT route_id, CAST (COLLECT(DISTINCT route_seq) AS KU$_OBJNUMSET) rs_part
FROM route
GROUP BY route_id)
SELECT route_id, rs_ges MULTISET EXCEPT rs_part AS rs_miss
FROM rges, rs;
ROUTE_ID RS_MISS(ELEMENT)
---------- ------------------
123 KU$_OBJNUMSET(2,4)
456 KU$_OBJNUMSET(3)
789 KU$_OBJNUMSET(3,4)
3 rows selected.
[Updated on: Wed, 27 January 2016 01:41] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Determine Missing Route_Seq Numbers [message #647420 is a reply to message #647345] |
Wed, 27 January 2016 05:55 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Recursive solution pin-pointed to generate just missing route sequences versus generating whole range and "minusing" existing:
with t as (
select route_id,
route_seq + 1 gap_start_route_seq,
lead(route_seq,1,route_seq) over(partition by route_id order by route_seq) - 1 gap_end_route_seq
from route
),
r(
route_id,
route_seq,
gap_end_route_seq
) as (
select route_id,
gap_start_route_seq route_seq,
gap_end_route_seq
from t
where gap_start_route_seq <= gap_end_route_seq
union all
select route_id,
route_seq + 1 route_seq,
gap_end_route_seq
from r
where route_seq < gap_end_route_seq
)
select route_id,
route_seq
from r
/
ROUTE_ID ROUTE_SEQ
---------- ----------
123 2
456 3
SQL>
Model solution if recursive subquery factoring is not available (10G):
with t as (
select route_id,
route_seq + 1 gap_start_route_seq,
lead(route_seq,1,route_seq) over(partition by route_id order by route_seq) - 1 gap_end_route_seq
from route
)
select route_id,
route_seq
from t
where gap_start_route_seq <= gap_end_route_seq
model
return updated rows
partition by(route_id,gap_start_route_seq)
dimension by(1 d)
measures(gap_start_route_seq route_seq,gap_end_route_seq)
rules(
route_seq[for d from route_seq[1] to gap_end_route_seq[1] increment 1] = cv(d)
)
/
ROUTE_ID ROUTE_SEQ
---------- ----------
456 3
123 2
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
