1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

findout missing number throught out sequential numbers ?

Discussion in 'SQL PL/SQL' started by ora-00100, Feb 16, 2014.

  1. ora-00100

    ora-00100 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    i have a table that contains around 2 million records and it contains a column which has sequential number starting from 1 and I want find out the missing numbers in that sequence... can anyone help me ??


    thanks
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You could write a PL/SQL block to do this and it will work on any release from 6 onwards. There may be easier ways to do this but those are version dependent.


    Why do you want to find 'missing' sequence numbers?
     
  4. ora-00100

    ora-00100 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a millions of string record like this one with 36 type of them that have different format to get sequence,year,month and day from..

    emp-1111_14_01_01_1111_G1

    emp-1111_14_01_01_1112_G1

    emp-1111_14_01_01_1109_G1

    emp-1111_14_01_01_1115_G1

    emp-1111_14_01_01_1110_G2

    emp-1111_14_01_01_1105_G1

    emp-1111_14_01_01_1120_G2

    This script will get the sequence,year,month and day... now I want a procedure that will get the max and min value number of the sequence and find the missing number where is year and month are for example 14 - 06 how ??


    declare
    v_name table1.ENAME%TYPE;
    V_seq NUMBER (4);
    V_Year number(2);
    V_Month number (2);
    V_day number (2);
    max_seq number(4);
    min_seq number(4);

    CURSOR List_ENAME_cur IS
    SELECT ENAME from table1
    WHERE status = 2;
    begin

    FOR List_ENAME_rec IN List_ENAME_cur loop
    if REGEXP_LIKE(List_ENAME_cur.ENAME,'emp[-][1-9]{4}[_][1-9]{2}[_][1-9]{2}[_][1-9]{2}[_][0-9]{4}[_][G]["1"]') then
    V_seq := substr(List_ENAME_cur.ename,5,4);
    V_Year := substr(List_ENAME_cur.ename,10,2);
    V_Month := substr(List_ENAME_cur.ename,13,2);
    V_day := substr(List_ENAME_cur.ename,16,2);


    if min_seq is null or V_seq < min_seq then
    min_seq := v_seq;
    end if;
    if max_seq is null or V_seq > max_seq then
    max_seq := v_seq;
    end if;


    -- new block update
    if min_seq is null then
    DBMS_OUTPUT.PUT_LINE('you missed me' || min_seq+1);
    end if;


    end if;
    end loop;

    DBMS_OUTPUT.PUT_LINE('max_seq '||max_seq||' min_seq '||min_seq);

    **-- procedure call.....**

    end;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What sort of 'sequence' is this? If there are 36 different formats it isn't a sequence. How are you generating these values? Why are you generating such strings? There are better ways to do this using date fields and actual numeric sequence values.


    Please explain the 'logic'.