gaps between item numbers

[kermit01de]

Hi there,
we have an SQLtable "item" that contains a full range of items. We usually assign 6-digit-numbers as [No_].

I already have a script, that finds free numbers:

Select
  t.[No_]+1 as LueckeVon,
  (Select MIN(CAST([No_] as int))-1 From [BODAN600].[dbo].[BODAN Gh_ für Naturkost GmbH$Item] Where CAST([No_] as int)>CAST(t.[No_]as int)) as LueckeBis
From 
  [BODAN600].[dbo].[BODAN Gh_ für Naturkost GmbH$Item] t
Where
  (Select MIN(CAST([No_] as int))-CAST(t.[No_] as int) From [BODAN600].[dbo].[BODAN Gh_ für Naturkost GmbH$Item] Where CAST([No_] as int)>CAST(t.[No_]as int))>1

The problem is: Of some reason there are Items that have item.[No_] like "IT-0001", "AGILES", "PZ-0001" etc. So if I run the script on the live-db I get the error

Msg 245, Level 16, State 1, Line 2
Fehler beim Konvertieren des varchar-Werts 'AGILES-DL' in den int-Datentyp.

So I usually restored the db on the testsystem, deleted the alpha-items and let the script run. Unfortunately this is not possible at the moment, because there are several things to test on the test-db, and restore would overwrite the test-objects.

Any idea how to filter out the numeric and only use these for gap-analysis.

Thank you!


Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[bborissov]

Check ISNUMERIC() function

https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15

Borislav Borissov
VFP9 SP2, SQL Server

 

[kermit01de]

Thank you Borislav,
I already checked it, but as I am no SQL-Programmer - how to integrate in the existing script? I tried several things - without success.
Any hint?

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[bborissov]

WHERE ISNUMERIC([No_]) = 1

NOT TESTED!!!!!!

Borislav Borissov
VFP9 SP2, SQL Server

 

[kermit01de]

Unfortunately this does not work:

Select
  t.[No_]+1 as LueckeVon,
  (Select MIN(CAST([No_] as int))-1 From [BODAN600].[dbo].[BODAN Gh_ für Naturkost GmbH$Item] Where CAST([No_] as int)>CAST([No_]as int)) as LueckeBis
From 
  [BODAN600].[dbo].[BODAN Gh_ für Naturkost GmbH$Item] t
Where
	ISNUMERIC([No_]) = 1 AND
	(Select MIN(CAST([No_] as int))-CAST([No_] as int) From [BODAN600].[dbo].[BODAN Gh_ für Naturkost GmbH$Item] Where CAST([No_] as int)>CAST([No_]as int))>1

--> Msg 8120, Level 16, State 1, Line 10
Die BODAN600.dbo.BODAN Gh_ für Naturkost GmbH$Item.No_-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.


Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[bborissov]

I can not understand the error message

Borislav Borissov
VFP9 SP2, SQL Server