Operating System - HP-UX
1748183 Members
3493 Online
108759 Solutions
New Discussion юеВ

Re: Need a script , thanks for help!

 
SOLVED
Go to solution
violin_1
Advisor

Need a script , thanks for help!

Hello all fellows,

If I had a coulmn in number datatype and the value should be always sequential.
How could I get the value which is not in sequential?

ex.
SQL> select * from temp order by 1;

COL1
----------
0
1
2
3
6
8
9

7 rows selected.

SQL>

How could I get the lost number : 4 , 5 , 7 ?
Thanks for any tips.

Violin.
5 REPLIES 5
H.Merijn Brand (procura
Honored Contributor
Solution

Re: Need a script , thanks for help!

since you don't mention the database you're using, we can't use extra functionality built-in in some databases.

Personally I'd use perl/DBI, but I don't know if you have the corresponding driver installed, otherwise, after the connect to $dbh, you would have had something like

my ($min, $max, $col1, %val) = (999999, 0);
my $sth = $dbh->prepare ("select col1 from temp");
$sth->bind_columns (\$col1);
while ($sth->fetch) {
$val{$col1}++;
$val < $min and $min = $val;
$val > $max and $max = $val;
}
for ($min .. $max) {
exists $val{$_} or print "$_ missing\n";
}

otherwise you could create a temp2 table that has all values inserted from min(temp.col1) to max(temp.col1)

select col1 from temp2 where col1 not in (select col1 from temp);

Enjoy, Have FUN! H.Merijn
Enjoy, Have FUN! H.Merijn
violin_1
Advisor

Re: Need a script , thanks for help!

Sorry, the DB is Oracle 8.1.7.4.
Fred Ruffet
Honored Contributor

Re: Need a script , thanks for help!

PL/SQL version :

SQL> set serveroutput on
SQL> declare
2 tmpnum number(2);
3 maxval number(2);
4 curval number(2);
5 begin
6 tmpnum:=0;
7 select max(col1) into maxval from temp;
8 while (tmpnum 9 loop
10 select count(*) into curval from temp where col1=tmpnum;
11 if curval=0 then
12 dbms_output.put_line(tmpnum);
13 end if;
14 tmpnum:=tmpnum+1;
15 end loop;
16 end;
17 /
4
5
7

Proc├Г┬йdure PL/SQL termin├Г┬йe avec succ├Г┬иs.

Regards,
--

"Reality is just a point of view." (P. K. D.)
Yogeeraj_1
Honored Contributor

Re: Need a script , thanks for help!

hi,

is the case of lost numbers is a problem (should not be though!) then there must be a flaw in the piece of code generating it!

in practice, you would use a trigger + sequence to accomplish that.

You may also use a function to generate that for you.
e.g.
create or replace function get_nextval( p_name in varchar2 ) return number
as
l_id number;
begin
update ids set id = id+1 where name = upper(p_name)
returning id into l_id;

if ( sql%rowcount = 0 ) then
raise_application_error( -20001, 'No such id name ' || p_name );
end if;
return l_id;
end;
/


regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Thierry Poels_1
Honored Contributor

Re: Need a script , thanks for help!

hi,

if you are using sequences to generate this number you can never guarantee that the numbers will always sequential:

- if the sequence is created with "cache n entries" you will loose all entries in cache when the database is shutdowned.
- if the sequence is fetched, and then the transaction is rollbacked, then the fetched sequence entry is again lost.

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.