Missing Numbers (Between two numbers) - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 27

Thread: Missing Numbers (Between two numbers)

  1. #11
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Possibly "my_table" can be used to generate the ROWNUM set? Only Sathy will know that.

    Jurij, MINUS does look neater than my NOT IN.

  2. #12
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    However it is also obvious that your suggesten was equaly useless and wrong.
    Well, yes its useless.. was in haste.. & never thought of a case with gaps more than 1.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #13
    Join Date
    Jan 2004
    Posts
    162
    Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.

    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER (10);
      2  /
    
    Type created.
    
    SQL> CREATE TABLE table_name (
      2    column_name NUMBER (10));
    
    Table created.
    
    SQL> INSERT INTO table_name
      2  SELECT column_value
      3  FROM   TABLE (number_table (2, 4, 5, 8, 10));
    
    5 rows created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> VARIABLE min NUMBER;
    SQL> VARIABLE max NUMBER;
    SQL> EXEC :min := 1; :max := 10;
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT now + 1 || DECODE (now + 2, nxt, NULL, ' - ' || (nxt - 1)) gap
      2  FROM  (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
      3         FROM  (SELECT column_name now
      4                FROM   table_name
      5                UNION ALL
      6                SELECT column_value
      7                FROM   TABLE (number_table (:min - 1, :max + 1))))
      8  WHERE  now + 1 != nxt;
    
    GAP
    --------------------------------------------------------------------------
    1
    3
    6 - 7
    9
    
    4 rows selected.
    
    SQL>
    Of course to get all the values of the gaps themselves we are (as we have seen) going to have to synthesize some rows. An alternative to ALL_OBJECTS here is to use a table function. This gives us an interesting choice - we can either synthesize all the rows up front or we can synthesize rows on a row-by-row basis via the TABLE () syntax as required. First up is the row-by-row approach - perhaps if there are only a few gaps this could be a more efficient approach.
    Code:
    SQL> CREATE OR REPLACE FUNCTION many (
      2    p_rows IN NUMBER)
      3    RETURN number_table PIPELINED
      4  IS
      5  BEGIN
      6    FOR l_row IN 1..p_rows LOOP
      7      PIPE ROW (l_row);
      8    END LOOP;
      9    RETURN;
     10  END;
     11  /
    
    Function created.
    
    SQL> SELECT now + column_value now
      2  FROM  (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
      3         FROM  (SELECT column_name now
      4                FROM   table_name
      5                UNION ALL
      6                SELECT column_value
      7                FROM   TABLE (number_table (:min - 1, :max + 1)))), 
      8         TABLE (many (nxt - now - 1))
      9  WHERE  now + 1 != nxt;
    
           NOW
    ----------
             1
             3
             6
             7
             9
    
    5 rows selected.
    
    SQL>
    Alternatively we can (as mentioned) synthesize all the rows up-front using the table function and anti-join, outer-join or MINUS as suggested by Dapi and Jurij.
    Code:
    SQL> SELECT now
      2  FROM  (SELECT :MIN + column_value - 1 now
      3         FROM   TABLE (many (:max - :min + 1))) a,
      4         table_name b
      5  WHERE  column_name IS NULL
      6  AND    column_name(+) = now;
    
           NOW
    ----------
             1
             3
             6
             7
             9
    
    5 rows selected.
    
    SQL>
    If follows that we can extend these two examples to give us all the values (and also know which is fake or real).
    Code:
    SQL> SELECT DECODE (now, now - 1 + column_value, 'REAL', 'FAKE') src, 
      2         now - 1 + column_value now
      3  FROM  (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
      4         FROM   (SELECT column_name now
      5                 FROM   table_name
      6                 UNION ALL
      7                 SELECT column_value
      8                 FROM   TABLE (number_table (:min - 1, :max + 1)))), 
      9         TABLE (many (NVL (nxt - now, 1)))
     10  WHERE now - 1 + column_value NOT IN (:min - 1, :max + 1);
    
    SRC         NOW
    ---- ----------
    FAKE          1
    REAL          2
    FAKE          3
    REAL          4
    REAL          5
    FAKE          6
    FAKE          7
    REAL          8
    FAKE          9
    REAL         10
    
    10 rows selected.
    
    SQL> SELECT DECODE (column_name, NULL, 'FAKE', 'REAL') src, now
      2  FROM  (SELECT :MIN + column_value - 1 now
      3         FROM   TABLE (many (:max - :min + 1))) a,
      4         table_name b
      5  WHERE  column_name(+) = now;
    
    SRC         NOW
    ---- ----------
    FAKE          1
    REAL          2
    FAKE          3
    REAL          4
    REAL          5
    FAKE          6
    FAKE          7
    REAL          8
    FAKE          9
    REAL         10
    
    10 rows selected.
    
    SQL>

  4. #14
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "Any sufficiently advanced technology is indistinguishable from magic!"

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by padders
    Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.
    Yesss! That's the right answer.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by jmodic
    Yesss! That's the right answer.
    Analytic functions are the right answer to any question.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #17
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    The simple solution to find missing numbers is using cube:

    SQL> select * from t1;

    ID
    ----------
    10
    13
    14
    17
    19
    20

    6 rows selected.

    SQL>
    1 select rn
    2 from (select /*+ no_merge */ rownum as rn
    3 from ( select 1 from dual group by cube (1,1,1,1,1)) a,
    4 ( select 1 from dual group by cube (1,1,1,1,1,1)) b
    5 )
    6 where rn >= (select min(id) from t1) and
    7 rn <= (select max(id) from t1)
    8 minus
    9* select id from t1
    SQL> /

    RN
    ----------
    11
    12
    15
    16
    18

    Technically you can generate millions of millions of rows using cube.
    On my IBM P690 unix AIX machine (8 cpus with 1500 MH spped, 16 GB RAM), I generated 4 million rows table in 110 seconds.
    If you want to generate more number of rows, just add 1s in the cube function.

    Tamil

  8. #18
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Yes TamilSelvan..It works.. But it take lengthy time...

    select rn from
    (select rownum as rn from (
    select 1 from dual group by cube (1,1,1,1,1,1,1,1,1,1,1)) a,
    (select 1 from dual group by cube (1,1,1,1,1,1,1,1,1,1,1,1)) b)
    where rn >=(select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2)
    and rn <=(select max(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2)
    minus
    select to_number(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2

    Elapsed Time: 00:00:40.38

    Is any opttion to reduce...???
    Any Index Problem...???

  9. #19
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ==========
    select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2
    ==========
    You need to to tune the subquery.

    Do you have a composite index on daddt,datype, and dadno columns?

    Tamil

  10. #20
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    The simple solution to find missing numbers is using cube:
    I think that i would prefer to use either a list of numbers in a table or a pipelined function to generate the required values ...
    Code:
    create or replace type TypTabNum as table of number
    /
    create or replace package utilities
    as
      function tab_of_numbers (p_start number, p_finish number) return TypTabNum pipelined;
    end;
    /
    
    create or replace package body utilities
    as
       function tab_of_numbers (p_start number, p_finish number)
          return TypTabNum
          pipelined
       is
       begin
          if p_finish > p_start
          then
             for i in p_start .. p_finish 
             loop
                pipe row (i);
             end loop;
          else
             for i in reverse p_finish .. p_start
             loop
                pipe row (i);
             end loop;
          end if;
       return;
       end;
    end;
    /
    
    select * from table (utilities.tab_of_numbers(1,10))
    /
    
    select * from table (utilities.tab_of_numbers(10,1))
    /
    So anyway, you could ...
    Code:
    create table my_table
       (my_number number);
       
    insert into my_table values (3);
    insert into my_table values (4);
    insert into my_table values (8);
    insert into my_table values (10);
    insert into my_table values (12);
    
    commit;
    
    select
       column_value as missing
    from
       table (utilities.tab_of_numbers(
          (select min(my_number) from my_table),
          (select max(my_number) from my_table)))
    minus
    select
       my_number
    from
       my_table
    /
    
       MISSING
    ----------
             5
             6
             7
             9
            11
    Just depends what kind of result set you want ... analytical functions are good for identifying ranges "5-7", this is good for listing individual values.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width