Ann,

Thank you for responding. I supplied Mike with the code he needed a couple
of days ago. I did so privately.

Bill Meaney

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Monday, July 27, 2009 6:29 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Looking for a Firebird Stored Procedure to
return a string showing 1-20,24,26-27,30


SoftTech wrote:

> Never Mind, I will do this myself

Unlike UDF's, stored procedures must integrate the
database schema they work on (generally, except the
very simplest ones). So where we do have libraries
of useful user defined functions, a new procedure
must be written by hand, which makes them less fun
to provide on request.

As for tone, do remember that not all of us are native
speakers and sometimes what sounds like light hearted
chaffing to one person sounds insulting to another. And
we all have bad days.

The approximate algorithm you need is

create procedure foo (returns bar varchar(100)
as
declare v integer;
declare first integer;
declare x integer;
begin
bar = '(';
v = -1;
first = 0;

for select (a) from t1 order by a into x
do begin

/* if this is the very first item, print it */

if (first = 0)
begin
bar = bar || x;
first = 1;
v = x;
end

/* if this is the first sequential number print a dash */
else if (x == v + 1)
begin
if (first = 1)
begin
bar = bar || '-';
first = 2
end
/* if it is a subsequent duplicate do nothing */
x = v;
end
/* if it's out of sequence, print old and new values */
else
begin
bar = bar || x || ',' || v;
x = v;
first = 1;
end
end
bar = bar || ')'
end

Cheers,

Ann



[Non-text portions of this message have been removed]