
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.

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"

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 rowbyrow basis via the TABLE () syntax as required. First up is the rowbyrow 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 upfront using the table function and antijoin, outerjoin 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>

"Any sufficiently advanced technology is indistinguishable from magic!"

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?

Originally posted by jmodic
Yesss! That's the right answer.
Analytic functions are the right answer to any question.

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

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>=sysdate2)
and rn <=(select max(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate2)
minus
select to_number(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate2
Elapsed Time: 00:00:40.38
Is any opttion to reduce...???
Any Index Problem...???

==========
select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate2
==========
You need to to tune the subquery.
Do you have a composite index on daddt,datype, and dadno columns?
Tamil

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 "57", this is good for listing individual values.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
