[AccessD] Checking for "holes" in a sequence using SQL

Hello Gustav,

Really beautiful. Compliments to you.  Of course when we read the SQL
now, it is simple, but not so simple when we were the other side of
the fence.

I love to hand write my SQL when practical.

Mark




On Thu, 4 Nov 2004 17:51:23 +0530, A.D.Tejpal <adtp at touchtelindia.net> wrote:
>    Excellent - Gustav!   Beautiful SQL.
> 
> A.D.Tejpal
> --------------
> 
> 
> 
>  ----- Original Message -----
>  From: Gustav Brock
>  To: Access Developers discussion and problem solving
>  Sent: Thursday, November 04, 2004 15:22
>  Subject: Re: [AccessD] Checking for "holes" in a sequence using SQL
> 
>  Hi Joe
> 
>  > If I have data similar to:
>  > JOE001
>  > JOE002
>  > JOE003
>  > JOE005
>  > JOE006
> 
>  > Is there a way, using just SQL, to return the next available number in the
>  > sequence, JOE004, and be flexible enough to return JOE007 if JOE004 had
>  > already existed?
>  > Or do I have to use code to iterate through the records to check for
>  > "holes"?
> 
>  Except for the indication of some malfunctioning during the design
>  state as already pointed out by others, there is:
> 
>  <SQL>
> 
>    SELECT TOP 1
>      TextID,
>      Left(TextID, Len(TextID)-3) &
>        Right(String(3,"0") & Right(TextID,3)+1,3) AS NextID
>    FROM
>      tblID
>    WHERE
>      (Nz(
>        (Select Top 1
>          Val(Right(TextID,3))
>        From
>          tblID As T
>        Where
>          T.TextID > tblID.TextID)
>        -Val(Right([TextID],3)),
>      2)>1)
>    ORDER BY
>      tblID.TextID;
> 
>  </SQL>
> 
>  All the "3"s count for the length of the numeric right part of the
>  textid.
>  Note the "magic 2" in Nz() which cares for the return of the last
>  textid if no "holes" exist.
> 
>  /gustav
> 
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>