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!

find the gaps in between the sequences in 8i

Discussion in 'SQL PL/SQL' started by SW26477, Nov 13, 2014.

  1. SW26477

    SW26477 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello All,

    I have a query with finding the gap in between the sequences.

    Below is the structure of my table, having 8i database, so can't use Lag.
    Code (Text):

    user_number         |    seq_number
    ----------------------------------
    1                   |       101
    1                   |       102
    1               |   109
    1               |   110
    1               |   111
    2               |   201
    2               |   202
    2               |   203
    2               |   207
    2               |   208
    .               |   .
    .               |   .
    .               |   .
    .               |   .

     
    Now I have to find the gap in between the sequences, what I mean by gap is for user_number '1' there is gap of 7 between seq_number '102' and '109'.

    Below is the output which I want:

    Code (SQL):

    USER_NUMBER | SEQ_NUMBER      |  DIFF
    ------------|-----------------|----------
              1           |         109                      |               7
              2           |         207                        |                4
     
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

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

    the solution of sql is necessary?

    Not to use analytics is a condition of your task?

    for example (pl/sql) :
    Code (SQL):


    DECLARE
        i NUMBER;
        j NUMBER;
    BEGIN    
        FOR z IN (
                    SELECT  1 usernumber,101 seq_number FROM dual UNION ALL
                    SELECT  1 ,102  FROM dual UNION ALL
                    SELECT  1 ,109  FROM dual UNION ALL
                    SELECT  1 ,110  FROM dual UNION ALL
                    SELECT  2 ,203  FROM dual UNION ALL
                    SELECT  2 ,209  FROM dual
                    ORDER  BY  1,2                    
                )
        loop
           
            IF nvl(i,z.usernumber) = z.usernumber AND z.seq_number - nvl(j,z.seq_number) > 1 THEN
                dbms_output.put_line('Found = '||z.seq_number||' - '||j);
            END IF;

            i:= z.usernumber; j:=z.seq_number;
           
        END loop;            
    END;
    /


    Found = 109 - 102
    Found = 209 - 203

     
     
    SW26477 likes this.
  3. SW26477

    SW26477 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Wow!!!!

    Thanks krasnoslobodtsev_si!!!

    This is what I was searching for. Thanks once again.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For example (sql) :
    Code (SQL):

        SELECT
              t.rn,
              t.usernumber,
              t.seq_number,
              prior t.seq_number
        FROM
            (
            SELECT rownum rn ,t1.* FROM (
                                        SELECT  1 usernumber,101 seq_number FROM dual UNION ALL
                                        SELECT  1 ,102  FROM dual UNION ALL
                                        SELECT  1 ,109  FROM dual UNION ALL
                                        SELECT  1 ,110  FROM dual UNION ALL
                                        SELECT  2 ,203  FROM dual UNION ALL
                                        SELECT  2 ,209  FROM dual UNION ALL
                                        SELECT  3 ,303  FROM dual UNION ALL    
                                        SELECT  3 ,304  FROM dual UNION ALL
                                        SELECT  3 ,309  FROM dual                                    
                                        ORDER  BY  1,2        
                                      ) t1
            )  
         t
        WHERE t.seq_number - prior t.seq_number > 1 AND prior t.usernumber = t.usernumber
        START WITH t.rn = 1
        CONNECT BY prior t.rn = t.rn - 1;
                   
     
     
  5. vprog

    vprog Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Ghaziabad
    In order to find difference in series of numbers, there is no use of using Oracle software in the first place. We can use C++ in order to make this series. Use the number in array of constant length i.e. total number of integers in your series....This is the code in C++


    #include <iostream.h>
    void main()
    {
    int num1[12], num2[12], res[12], i;
    for (i = 1; i <= 12; i++)
    {
    cout << "Enter first number: ";
    cin >> num1;
    cout << "Enter second number: ";
    cin >> num2;
    res = num2 - num1;
    }
    for (i = 1; i <= 12; i++)
    cout << num1 << " " << num2 << " " << res << endl;
    }

    :hurray :)