The following applies:
A table with a indexed and unique integer field 'PRODUCTID'.
I would like to find and display all records in the table that represent
"holes" / available places in the existing sequence.
Example:

1
2
3
11
12
100
101

There are two holes in this sequence 4-10 and 13-99.
Anyone taht know how I can produce a result like this?

Thanks
Petter Topp
D 2007, Dbisam 4 C/S

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:08189CF8-3FA2-49B5-B3AC-F43107CD67C2@news.elevatesoft.com...
> The following applies:
> A table with a indexed and unique integer field 'PRODUCTID'.
> I would like to find and display all records in the table that represent
> "holes" / available places in the existing sequence.
> Example:
>
> 1
> 2
> 3
> 11
> 12
> 100
> 101
>
> There are two holes in this sequence 4-10 and 13-99.
> Anyone taht know how I can produce a result like this?
>

CREATE TABLE IF NOT EXISTS "t"
(
  "id" INTEGER,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "byid" ON "t" ("id");

INSERT INTO "t" VALUES (1);
INSERT INTO "t" VALUES (2);
INSERT INTO "t" VALUES (3);
INSERT INTO "t" VALUES (11);
INSERT INTO "t" VALUES (12);
INSERT INTO "t" VALUES (100);
INSERT INTO "t" VALUES (101);

select t1.id id1, t2.id id2
into memory\temp1
from t t1
left outer join t t2 on t1.id + 1  = t2.id
where t2.id = null;
alter table memory\temp1 add a autoinc;
update memory\temp1 t1 set t1.id2 = t2.id1 - 1 from memory\temp1 t1
join memory\temp1 t2 on t1.a = t2.a - 1;
delete from memory\temp1 where id2 = null;
select id1 + 1 FromID, id2 - 1 ToID from memory\temp1;

Robert

Hello Robert,

After this I have absolutely no doubts about your SQL capabilities.
This is way of over my head, and frankly, I was hoping that there were some
built in solutions for my task.

The only problem I see with your code is that it uses the actual data in the
SQL...
If I were to use this I would have to create the SQL at run time based on
dynamic data.

Thanks
Petter


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:D08F2178-D774-4D24-BF3F-E674F5F662D1@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:08189CF8-3FA2-49B5-B3AC-F43107CD67C2@news.elevatesoft.com...
>> The following applies:
>> A table with a indexed and unique integer field 'PRODUCTID'.
>> I would like to find and display all records in the table that represent
>> "holes" / available places in the existing sequence.
>> Example:
>>
>> 1
>> 2
>> 3
>> 11
>> 12
>> 100
>> 101
>>
>> There are two holes in this sequence 4-10 and 13-99.
>> Anyone taht know how I can produce a result like this?
>>
>
> CREATE TABLE IF NOT EXISTS "t"
> (
>   "id" INTEGER,
> PRIMARY KEY ("RecordID") COMPRESS NONE
> LOCALE CODE 0
> USER MAJOR VERSION 1
> );
>
> CREATE INDEX IF NOT EXISTS "byid" ON "t" ("id");
>
> INSERT INTO "t" VALUES (1);
> INSERT INTO "t" VALUES (2);
> INSERT INTO "t" VALUES (3);
> INSERT INTO "t" VALUES (11);
> INSERT INTO "t" VALUES (12);
> INSERT INTO "t" VALUES (100);
> INSERT INTO "t" VALUES (101);
>
> select t1.id id1, t2.id id2
> into memory\temp1
> from t t1
> left outer join t t2 on t1.id + 1  = t2.id
> where t2.id = null;
> alter table memory\temp1 add a autoinc;
> update memory\temp1 t1 set t1.id2 = t2.id1 - 1 from memory\temp1 t1
> join memory\temp1 t2 on t1.a = t2.a - 1;
> delete from memory\temp1 where id2 = null;
> select id1 + 1 FromID, id2 - 1 ToID from memory\temp1;
>
> Robert
>

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:1443A4FB-DEDE-4C43-8562-578476D216B2@news.elevatesoft.com...
>
> The only problem I see with your code is that it uses the actual data in
> the SQL...

That was just an example, your SQL would start on the select. But after
reading the script again, I think there's probably other problems. Do it in
code.

Robert

Note if you only need to know where a gap exists, you can really shorten the
code by just using the logic searching for previous value (except lowest)
HTH  Donat.

/* Find holes in sequences
  The existing low/high range are eliminated with Where clause at the end
 Changed name for our app .. you can also use temp tables & cleanup */

Select distinct(Identifier) as Identifier
into Temp
from SyReport Where Identifier > 0 Order by 1;

Select m.Identifier, (m.Identifier-1) as MissPrev
Into TempPrev
from Temp M
left join Temp pv on pv.Identifier = m.identifier -1
Where pv.Identifier is null
Order by 1 ;

Select m.Identifier, (M.Identifier+1) as MissNext
Into TempNext
from Temp M
left join Temp nv on nv.Identifier = m.Identifier+1
Where nv.Identifier is null
Order by 1 ;

Select m.Identifier, pv.MissPrev, nv.MissNext
Into Missing
from temp m
left join tempprev pv on pv.Identifier = m.Identifier
left join tempnext nv on nv.identifier = m.identifier
Where (pv.identifier > 0 or nv.identifier > 0 )
Order by 1;

Optimize table Missing;

Alter Table Missing
Add RecIdent AUTOINC at 1;

Select m.Identifier, m.MissNext as BegRange, lu.MissPrev as EndRange
From Missing M
left join Missing lu on lu.RecIdent = m.RecIdent +1
Where m.MissNext is not null
and m.MissNext is not null and lu.MissPrev is not null;

/*
drop table if exists temp;
drop table if exists tempprev;
drop table if exists tempnext;
drop table if exists missing;
*/





"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:08189CF8-3FA2-49B5-B3AC-F43107CD67C2@news.elevatesoft.com...
> The following applies:
> A table with a indexed and unique integer field 'PRODUCTID'.
> I would like to find and display all records in the table that represent
> "holes" / available places in the existing sequence.
> Example:
>
> 1
> 2
> 3
> 11
> 12
> 100
> 101
>
> There are two holes in this sequence 4-10 and 13-99.
> Anyone taht know how I can produce a result like this?
>
> Thanks
> Petter Topp
> D 2007, Dbisam 4 C/S
>
>
>

Hello Donat,

If you had the time, I would love to have an overall explenation on how you
approach this in your Query,
so that I perhaps can continue to work with it.

Regards
Petter

PS. I have actually come to the same conclusion as Robert, simply iterate
through the records using logic...



"Donat Hebert (Worldsoft)" <donat.hebert@worldsoft.ca> skrev i melding
news:95166C21-D519-4847-B86B-5E4EA165DFDF@news.elevatesoft.com...
> Note if you only need to know where a gap exists, you can really shorten
> the
> code by just using the logic searching for previous value (except lowest)
> HTH  Donat.
>
> /* Find holes in sequences
>   The existing low/high range are eliminated with Where clause at the end
>  Changed name for our app .. you can also use temp tables & cleanup */
>
> Select distinct(Identifier) as Identifier
> into Temp
> from SyReport Where Identifier > 0 Order by 1;
>
> Select m.Identifier, (m.Identifier-1) as MissPrev
> Into TempPrev
> from Temp M
> left join Temp pv on pv.Identifier = m.identifier -1
> Where pv.Identifier is null
> Order by 1 ;
>
> Select m.Identifier, (M.Identifier+1) as MissNext
> Into TempNext
> from Temp M
> left join Temp nv on nv.Identifier = m.Identifier+1
> Where nv.Identifier is null
> Order by 1 ;
>
> Select m.Identifier, pv.MissPrev, nv.MissNext
> Into Missing
> from temp m
> left join tempprev pv on pv.Identifier = m.Identifier
> left join tempnext nv on nv.identifier = m.identifier
> Where (pv.identifier > 0 or nv.identifier > 0 )
> Order by 1;
>
> Optimize table Missing;
>
> Alter Table Missing
> Add RecIdent AUTOINC at 1;
>
> Select m.Identifier, m.MissNext as BegRange, lu.MissPrev as EndRange
> From Missing M
> left join Missing lu on lu.RecIdent = m.RecIdent +1
> Where m.MissNext is not null
> and m.MissNext is not null and lu.MissPrev is not null;
>
> /*
> drop table if exists temp;
> drop table if exists tempprev;
> drop table if exists tempnext;
> drop table if exists missing;
> */
>
>
>
>
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:08189CF8-3FA2-49B5-B3AC-F43107CD67C2@news.elevatesoft.com...
>> The following applies:
>> A table with a indexed and unique integer field 'PRODUCTID'.
>> I would like to find and display all records in the table that represent
>> "holes" / available places in the existing sequence.
>> Example:
>>
>> 1
>> 2
>> 3
>> 11
>> 12
>> 100
>> 101
>>
>> There are two holes in this sequence 4-10 and 13-99.
>> Anyone taht know how I can produce a result like this?
>>
>> Thanks
>> Petter Topp
>> D 2007, Dbisam 4 C/S
>>
>>
>>
>
>

Notes follow.  Hope these help.  SQL logic better if lots of data and speed
is critical.
Donat.

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:71176980-6739-409F-84C1-115900E24880@news.elevatesoft.com...
> Hello Donat,
>
> If you had the time, I would love to have an overall explenation on how
> you approach this in your Query,
> so that I perhaps can continue to work with it.
>
> Regards
> Petter
>
> PS. I have actually come to the same conclusion as Robert, simply iterate
> through the records using logic...
>
>
>
> "Donat Hebert (Worldsoft)" <donat.hebert@worldsoft.ca> skrev i melding
> news:95166C21-D519-4847-B86B-5E4EA165DFDF@news.elevatesoft.com...
>> Note if you only need to know where a gap exists, you can really shorten
>> the
>> code by just using the logic searching for previous value (except lowest)
>> HTH  Donat.
>>
>> /* Find holes in sequences
>>   The existing low/high range are eliminated with Where clause at the end
>>  Changed name for our app .. you can also use temp tables & cleanup */
>>

....Obtain existing IDs, If this is PK, DBISAM 4 will retain the PK on the
data pull.

>> Select distinct(Identifier) as Identifier
>> into Temp
>> from SyReport Where Identifier > 0 Order by 1;
>>

... Self joining query to check for any numbers missing prior to current
number

>> Select m.Identifier, (m.Identifier-1) as MissPrev
>> Into TempPrev
>> from Temp M
>> left join Temp pv on pv.Identifier = m.identifier -1
>> Where pv.Identifier is null
>> Order by 1 ;
>>
... Self join checking for any numbers missing after current number

>> Select m.Identifier, (M.Identifier+1) as MissNext
>> Into TempNext
>> from Temp M
>> left join Temp nv on nv.Identifier = m.Identifier+1
>> Where nv.Identifier is null
>> Order by 1 ;
>>

Join to original list as master, any identifiers showing up as missing
before or after

>> Select m.Identifier, pv.MissPrev, nv.MissNext
>> Into Missing
>> from temp m
>> left join tempprev pv on pv.Identifier = m.Identifier
>> left join tempnext nv on nv.identifier = m.identifier
>> Where (pv.identifier > 0 or nv.identifier > 0 )
>> Order by 1;
>>

... Optimize will physically sort table by current PK .. essential to ensure
... we join by groups of 2

>> Optimize table Missing;
>>
... Add an auto increment to complete this step (numbers 1..n )

>> Alter Table Missing
>> Add RecIdent AUTOINC at 1;
>>

... Now you can put the result on one line with a simple join
... Insisting both records are not null eliminates the end values as they
... do not have a prior (first record) or next (last record)

.... Best way to understand is to look at outputs at each steps.

>> Select m.Identifier, m.MissNext as BegRange, lu.MissPrev as EndRange
>> From Missing M
>> left join Missing lu on lu.RecIdent = m.RecIdent +1
>> Where m.MissNext is not null
>> and lu.MissPrev is not null;
>>
>> /*
>> drop table if exists temp;
>> drop table if exists tempprev;
>> drop table if exists tempnext;
>> drop table if exists missing;
>> */
>>
>>
>>
>>
>>
>> "Petter Topp" <petter.topp@atcdata.no> wrote in message
>> news:08189CF8-3FA2-49B5-B3AC-F43107CD67C2@news.elevatesoft.com...
>>> The following applies:
>>> A table with a indexed and unique integer field 'PRODUCTID'.
>>> I would like to find and display all records in the table that represent
>>> "holes" / available places in the existing sequence.
>>> Example:
>>>
>>> 1
>>> 2
>>> 3
>>> 11
>>> 12
>>> 100
>>> 101
>>>
>>> There are two holes in this sequence 4-10 and 13-99.
>>> Anyone taht know how I can produce a result like this?
>>>
>>> Thanks
>>> Petter Topp
>>> D 2007, Dbisam 4 C/S
>>>
>>>
>>>
>>
>>
>
>

Super Donat,

I'm looking forward to try this first thing on monday.
Very grateful for your help.

Thanks
Petter

"Donat Hebert (Worldsoft)" <donat.hebert@worldsoft.ca> skrev i melding
news:CFFA443F-D6A5-49B5-88FE-CC7FDDC6B6D2@news.elevatesoft.com...
> Notes follow.  Hope these help.  SQL logic better if lots of data and
> speed is critical.
> Donat.
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:71176980-6739-409F-84C1-115900E24880@news.elevatesoft.com...
>> Hello Donat,
>>
>> If you had the time, I would love to have an overall explenation on how
>> you approach this in your Query,
>> so that I perhaps can continue to work with it.
>>
>> Regards
>> Petter
>>
>> PS. I have actually come to the same conclusion as Robert, simply iterate
>> through the records using logic...
>>
>>
>>
>> "Donat Hebert (Worldsoft)" <donat.hebert@worldsoft.ca> skrev i melding
>> news:95166C21-D519-4847-B86B-5E4EA165DFDF@news.elevatesoft.com...
>>> Note if you only need to know where a gap exists, you can really shorten
>>> the
>>> code by just using the logic searching for previous value (except
>>> lowest)
>>> HTH  Donat.
>>>
>>> /* Find holes in sequences
>>>   The existing low/high range are eliminated with Where clause at the
>>> end
>>>  Changed name for our app .. you can also use temp tables & cleanup */
>>>
>
> ...Obtain existing IDs, If this is PK, DBISAM 4 will retain the PK on the
> data pull.
>
>>> Select distinct(Identifier) as Identifier
>>> into Temp
>>> from SyReport Where Identifier > 0 Order by 1;
>>>
>
> .. Self joining query to check for any numbers missing prior to current
> number
>
>>> Select m.Identifier, (m.Identifier-1) as MissPrev
>>> Into TempPrev
>>> from Temp M
>>> left join Temp pv on pv.Identifier = m.identifier -1
>>> Where pv.Identifier is null
>>> Order by 1 ;
>>>
> .. Self join checking for any numbers missing after current number
>
>>> Select m.Identifier, (M.Identifier+1) as MissNext
>>> Into TempNext
>>> from Temp M
>>> left join Temp nv on nv.Identifier = m.Identifier+1
>>> Where nv.Identifier is null
>>> Order by 1 ;
>>>
>
> Join to original list as master, any identifiers showing up as missing
> before or after
>
>>> Select m.Identifier, pv.MissPrev, nv.MissNext
>>> Into Missing
>>> from temp m
>>> left join tempprev pv on pv.Identifier = m.Identifier
>>> left join tempnext nv on nv.identifier = m.identifier
>>> Where (pv.identifier > 0 or nv.identifier > 0 )
>>> Order by 1;
>>>
>
> .. Optimize will physically sort table by current PK .. essential to
> ensure
> .. we join by groups of 2
>
>>> Optimize table Missing;
>>>
> .. Add an auto increment to complete this step (numbers 1..n )
>
>>> Alter Table Missing
>>> Add RecIdent AUTOINC at 1;
>>>
>
> .. Now you can put the result on one line with a simple join
> .. Insisting both records are not null eliminates the end values as they
> .. do not have a prior (first record) or next (last record)
>
> ... Best way to understand is to look at outputs at each steps.
>
>>> Select m.Identifier, m.MissNext as BegRange, lu.MissPrev as EndRange
>>> From Missing M
>>> left join Missing lu on lu.RecIdent = m.RecIdent +1
>>> Where m.MissNext is not null
>>> and lu.MissPrev is not null;
>>>
>>> /*
>>> drop table if exists temp;
>>> drop table if exists tempprev;
>>> drop table if exists tempnext;
>>> drop table if exists missing;
>>> */
>>>
>>>
>>>
>>>
>>>
>>> "Petter Topp" <petter.topp@atcdata.no> wrote in message
>>> news:08189CF8-3FA2-49B5-B3AC-F43107CD67C2@news.elevatesoft.com...
>>>> The following applies:
>>>> A table with a indexed and unique integer field 'PRODUCTID'.
>>>> I would like to find and display all records in the table that
>>>> represent "holes" / available places in the existing sequence.
>>>> Example:
>>>>
>>>> 1
>>>> 2
>>>> 3
>>>> 11
>>>> 12
>>>> 100
>>>> 101
>>>>
>>>> There are two holes in this sequence 4-10 and 13-99.
>>>> Anyone taht know how I can produce a result like this?
>>>>
>>>> Thanks
>>>> Petter Topp
>>>> D 2007, Dbisam 4 C/S
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Petter,

<< PS. I have actually come to the same conclusion as Robert, simply iterate
through the records using logic... >>

This is the type of thing that screams for cursor handling such as what is
available in EDB's procedures, and soon to be, scripts.  It allows you to
execute everything on the server while still allowing for row-by-row
navigation.

--
Tim Young
Elevate Software
www.elevatesoft.com