Oracle Analytic Function LAG to Review Sequence Gaps
-- Using the Oracle analytic function LAG(), we can
-- analyze gaps (9,11 and 12,20) in a sequence of 
-- numbers as shown in query below.

WITH q_base AS
 (SELECT level idx, 
   DECODE(MOD(level,10),0,level+level,level) ref_no
  FROM DUAL
  CONNECT BY level <=12),
 q_data AS
 (SELECT idx, ref_no,
    LAG(ref_no) OVER(ORDER BY ref_no) lag_no
  FROM q_base
  ORDER BY ref_no)
SELECT rownum no_,
 ref_no,
 CAST(DECODE(ref_no-NVL(lag_no,0),1,NULL, 'GAP -> '||
       lag_no||' - '||ref_no) AS VARCHAR2(20)) seq_info 
FROM q_data;


Analytic Function LAG to Review Sequence Gaps

Refer Calendar Days Lead/Lag

Oracle registered trademark of Oracle Corporation.

Last Revised On: Sptember 01, 2014

  69931