Hi all, I am creating a search table where the keywords field is made
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.
declare @ptr1 varbinary(16)
declare @Ptr2 varbinary(16)
declare @profileid int
declare @x int
set @profileid = 0
while @profileid is not null
begin
select
@profileid = min([id]),
@ptr1 = MIN(textptr(tex t1))
from #holding
where [id] @profileid
declare c2 cursor fast_forward for
select textptr(searcht erms), datalength(sear chterms)
from search
where search.[id] = @profileid
open c2
fetch c2 into @ptr2, @x
while @@fetch_status = 0
begin
updatetext search.searchte rms @ptr2 null 0 #holding.text1 @ptr1
fetch c2 into @ptr2, @x
end
close c2
deallocate c2
end
The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.
i.e.
search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl
When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?
Dan
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.
declare @ptr1 varbinary(16)
declare @Ptr2 varbinary(16)
declare @profileid int
declare @x int
set @profileid = 0
while @profileid is not null
begin
select
@profileid = min([id]),
@ptr1 = MIN(textptr(tex t1))
from #holding
where [id] @profileid
declare c2 cursor fast_forward for
select textptr(searcht erms), datalength(sear chterms)
from search
where search.[id] = @profileid
open c2
fetch c2 into @ptr2, @x
while @@fetch_status = 0
begin
updatetext search.searchte rms @ptr2 null 0 #holding.text1 @ptr1
fetch c2 into @ptr2, @x
end
close c2
deallocate c2
end
The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.
i.e.
search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl
When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?
Dan
Comment