Link to home
Start Free TrialLog in
Avatar of ottenm
ottenm

asked on

add new constraint to existing query

In SQL 2000 I need to add an additional constraint to a query I use that finds unused numeric PK's in a varchar field.  

The current query (below) comes from angelll's solution here:
https://www.experts-exchange.com/questions/23659182/HT-manage-numeric-keys-in-a-varchar-field-MS-SQL-2000.html

The constraint I need to add is: return only those values that don't already exist in et_objects.object_id (another table/column).  Unfortunately, in that table the values have an 's' prepended in front of them.  

For example, if the first unused integer in customers.customer_code is 123 (returned by the current query) but et_objects.object_id contains 's123' and 's124' then I need to return 125 instead.

Any help greatly appreciated.

Here is the current query that returns the smallest unused integer in customers.customer_code:

declare @t table ( v int )
insert into @t (v) select CAST( customer_code AS INT)
  FROM customers
 WHERE customer_code NOT LIKE '%[^0-9]%'
 
select min(t.v) + 1
  from @t t
  left join @t n
    on n.v = t.v + 1
 where n.v is null

Avatar of nicolasdiogo
nicolasdiogo
Flag of United Kingdom of Great Britain and Northern Ireland image

on your column you can do

select replace( yourCol, 's', '' ) from yourTable


select
replace( n, 's', '' )

from(

select n = 's134'

) src
Avatar of ottenm
ottenm

ASKER

Thank you nicolasdiogo.  I can do 'replace', or just concatenate.  What I need help with is getting the query to avoid values that conflict with the other table.
ASKER CERTIFIED SOLUTION
Avatar of ottenm
ottenm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial