topic Need a script , thanks for help! in Operating System - HP-UX

Need a script , thanks for help!

violin_1 — Mon, 08 Nov 2004 03:01:21 GMT

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.

Re: Need a script , thanks for help!

H.Merijn Brand (procura — Mon, 08 Nov 2004 03:11:35 GMT

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

Re: Need a script , thanks for help!

violin_1 — Mon, 08 Nov 2004 03:13:49 GMT

Sorry, the DB is Oracle 8.1.7.4.

Re: Need a script , thanks for help!

Fred Ruffet — Mon, 08 Nov 2004 04:11:49 GMT

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,

Re: Need a script , thanks for help!

Yogeeraj_1 — Mon, 08 Nov 2004 04:55:15 GMT

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

Re: Need a script , thanks for help!

Thierry Poels_1 — Mon, 08 Nov 2004 06:34:31 GMT

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.