-- 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;
69931