> Hi
>
> I have a table with a VarChar(20) field. It is commonly used for
> storing numbers but (obviously) can also be used to store Chars. I

want

> to find the highest numeric value in this field (so I can add a new
> record with a unique numeric value i.e. max + 1).

Rob,

Selecting a client code by looking for max + 1 is really not a good
idea for a number of reasons.

1. It is a slow operation, especially if there is no descending index
available.
2. It is not multi-transaction safe. If another user has already
inserted something with this client code, but has not yet commit then
you will not know this.

Firebird provides generators to create unique bigints. Personally I
would use a bigint field rather than a varchar to store it, but if
that is too much hassle, the varchar is large enough that you
probably will never roll over.

Note that the following code is just a suggestion, I haven't tested
it, but it will fix the way it is allocated and give a performance
increase to boot.

CREATE GENERATOR GEN_CLIENTCODE;

SET TERM ^ ;

CREATE PROCEDURE TMP_INITIALISECLIENTCODE
AS
DECLARE VARIABLE CLIENTCODE VARCHAR(20);
DECLARE VARIABLE MAXCODE BIGINT;
DECLARE VARIABLE CURRENTCODE BIGINT;
DECLARE VARIABLE STMT VARCHAR(100);
BEGIN
MAXCODE = 0;
FOR
SELECT ClientCode
FROM Client
WHERE ClientCode < 'A'
INTO :CLIENTCODE
DO
BEGIN
BEGIN
CURRENTCODE = CAST(:CLIENTCODE AS BIGINT);
IF (:CURRENTCODE > :MAXCODE) THEN
BEGIN
MAXCODE = :CURRENTCODE;
END

WHEN ANY DO
BEGIN
-- Could not cast as bigint, so who cares, not a bigint
-- should probably catch the actual exception rather than any
END
END

STMT = 'SET GENERATOR GEN_CLIENTCODE TO ' || :MAXCODE;
EXECUTE STATEMENT STMT;
END
^

SET TERM ;
^

COMMIT WORK;

EXECUTE PROCEDURE TMP_INITIALISECLIENTCODE;

COMMIT WORK;

DROP PROCEDURE TMP_INITIALISECLIENTCODE;

COMMIT WORK;

To allocate a new CLIENTCODE, you can now run the following query

SELECT CAST(GEN_ID(GEN_CLIENTCODE,1) AS VARCHAR(20))
FROM RDB$DATABASE

or if you change the field to a bigint

SELECT GEN_ID(GEN_CLIENTCODE,1)
FROM RDB$DATABASE

Adam