Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
Next available value |
Thu, Aug 23 2007 9:22 AM | Permanent Link |
"Petter Topp" | 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 |
Thu, Aug 23 2007 4:20 PM | Permanent Link |
"Robert" | "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 |
Fri, Aug 24 2007 3:58 AM | Permanent Link |
"Petter Topp" | 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 > |
Fri, Aug 24 2007 7:39 AM | Permanent Link |
"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 |
Fri, Aug 24 2007 10:57 AM | Permanent Link |
"Donat Hebert \(Worldsoft\)" | 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 > > > |
Fri, Aug 24 2007 11:44 AM | Permanent Link |
"Petter Topp" | 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 >> >> >> > > |
Fri, Aug 24 2007 11:53 AM | Permanent Link |
"Donat Hebert \(Worldsoft\)" | 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 >>> >>> >>> >> >> > > |
Fri, Aug 24 2007 1:55 PM | Permanent Link |
"Petter Topp" | 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 >>>> >>>> >>>> >>> >>> >> >> > > |
Fri, Aug 24 2007 6:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |