[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23659182.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

0
ottenm
Asked:
ottenm
  • 2
1 Solution
 
nicolasdiogoCommented:
on your column you can do

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


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

from(

select n = 's134'

) src
0
 
ottenmAuthor Commented:
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.
0
 
ottenmAuthor Commented:
I think I got it!  Id' been trying to add it to the insert, but came up with the following by adding it to the select.  Just need to test it out....

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
  left join et_objects o
    on o.object_id = 's' + cast(t.v + 1 as varchar)
 where n.v is null and o.object_id is null
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now