September 5, 2006 at 2:38 pm
declare @errors table (
erroriD varchar (20),
errorMessage varchar (100))
insert @errors select '1', 'unable to resolve address'
insert @errors select '2', 'wrong birthDATE'
----------------------------------------------------------------------------------------------------------------------------------------
declare @addressLookup table (
address varchar (100))
insert @addressLookup
select'water'union all
select'29 street'union all
select'us route 103'
----------------------------------------------------------------------------------------------------------------------------------------
declare @exception table (
addressException varchar (100),
errorID varchar (100))
----------------------------------------------------------------------------------------------------------------------------------------
declare @address table (
addressName varchar (100)
)
insert @address
select 'route50 st street'union all
select 'sand circuit' union all
select 'water st'union all
select 'us route 103'union all
select '29 STREET'
----------------------------------------------------------------------------------------------------------------------------------------
insert @exception(addressException, errorID)
select CASE
when substring(a.addressName, 1, len(a.addressName) - charindex(' ', reverse(a.addressName))+1) not in (select address from @addresslookup al)
then substring(a.addressName, 1, len(a.addressName) - charindex(' ', reverse(a.addressName)))
end, '1'
from @address a
select * from @exception
-----------------------------------------------------------------------------------------------------
I have to write a SP that should return only the addresses that don't exist in the @addressLookUp and put them in the @exception table.
What i have written is not what i want. Can someone help me out?
September 5, 2006 at 3:33 pm
Do an outer join to find those not there, for example
select a.id
from tbl1 a
LEFT OUTER JOIN tbl2 b on a.id=b.id
where b.id is null
will return only ids from rows in tbl1 that are not in tbl2.
And this is more correctly a T-SQL problem not administration.
September 5, 2006 at 3:37 pm
Change the Insert statement like this
insert
@exception(addressException, errorID)
select
substring(a.addressName, 1, len(a.addressName) - charindex(' ', reverse(a.addressName))), '1'
from
@address a where substring(a.addressName, 1, len(a.addressName) - charindex(' ', reverse(a.addressName))) not in
(
select
substring(a.address, 1, len(a.address) - charindex(' ', reverse(a.address))) from @addresslookup a
inner
join @address b
on
b.addressname like substring(a.address, 1, len(a.address) - charindex(' ', reverse(a.address))) +'%'
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply