Identificando perda de sequencia

[rmanParticipante]

@Hudson

Já time o mesmo problema, e foi bacana resolver, segue a solução:


CREATE TABLE SEQ(
ID NUMBER
);



INSERT INTO SEQ(ID) VALUES(1);
INSERT INTO SEQ(ID) VALUES(6);
INSERT INTO SEQ(ID) VALUES(10);
INSERT INTO SEQ(ID) VALUES(11);
INSERT INTO SEQ(ID) VALUES(12);
INSERT INTO SEQ(ID) VALUES(20);

COMMIT;



WITH SEQ_TEMP AS (
SELECT ROWNUM ID,X.SEQUENCIA
FROM(
SELECT ID SEQUENCIA
FROM SEQ
WHERE ID >= 1
UNION
SELECT -1
FROM DUAL
WHERE NOT EXISTS(
SELECT 1
FROM SEQ
WHERE ID = 1
)
ORDER BY 1
) X
ORDER BY 1
)
SELECT
CASE
WHEN N1.SEQUENCIA 0 THEN
CASE
WHEN N2.SEQUENCIA - N1.SEQUENCIA - 1 = 1 THEN
CAST(N1.SEQUENCIA + 1 AS VARCHAR2(16))
ELSE
CAST(N1.SEQUENCIA + 1 AS VARCHAR2(16))||' ATÉ '||CAST(N2.SEQUENCIA - 1 AS VARCHAR2(16))
END
END SEQUENCIA
,CASE
WHEN N1.SEQUENCIA 0 THEN
N2.SEQUENCIA - N1.SEQUENCIA - 1
END TOTAL
FROM SEQ_TEMP N1
INNER JOIN SEQ_TEMP N2 ON N2.ID = N1.ID + 1
WHERE N2.SEQUENCIA - N1.SEQUENCIA - 1 > 0
ORDER BY N1.SEQUENCIA;
;