Friday, October 24, 2008

SQL for Missing Sequence Number

SELECT invno,next_invno,
decode(next_invno,invno+1,'',decode(invno+1,next_invno-1,to_char(invno+1),to_char(invno+1)||'-'||to_char(next_invno-1))) missing_number
FROM (SELECT invno, LEAD(invno,1) OVER (ORDER BY invno) as next_invno FROM silinvoice)
WHERE next_invno IS NOT NULL
AND decode(next_invno,invno+1,'',decode(invno+1,next_invno-1,to_char(invno+1),to_char(invno+1)||'-'||to_char(next_invno-1))) IS NOT NULL

No comments: