Help with cursor statement
HI
I have a database that holds dog data information. One of the tables is a textnote table that holds free text pertaining to a dog. the table has the followig fields - dog_id, text_id, seq_no and text.Each time a textnote is entered against a dog it gets a text_id and a seq_no depending on how many lines the textnote is. I am wanting concatenate all the the sequence no's together relating to the text_id and then concatenate all the text fields of the the indevidual text_id into one line seperated by ****.
What I have done is:
concatenate all seq_no of a text_id into one line using the following statement:
CREATE FUNCTION dbo.udf_StripBlanks
(@Var1 VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN (SELECT RTRIM(
LTRIM(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
@Var1
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
,SPACE(2),SPACE(1))
)
)
)
END
GO
CREATE FUNCTION dbo.udf_AssemblePT
(@ot_Text_ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
--===== Declare the return variable to hold the concatenated parts in
DECLARE @Return VARCHAR(8000)
--===== Concatenate the various parts together in order by sequence number
-- This assumes that you want a space to separate the various parts and
-- that you want to exercise the delete dupe blanks function
SELECT @Return = ISNULL(@Return+' ','')+dbo.udf_StripBlanks(ot_text)
FROM dg_o_text
WHERE ot_text_id = @ot_Text_ID
ORDER BY ot_seq
--===== Return the concatenated parts for this Pt_Text_ID
RETURN @Return
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[z_temp_dogowner_text]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [z_temp_dogowner_text]
GO
SELECT DISTINCT
ot_Text_ID AS Text_ID,
dbo.udf_AssemblePT(ot_Text_ID) AS Text_IN
INTO z_temp_dogowner_text
FROM dg_o_text
ORDER BY ot_Text_ID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[tbl_Oz_DR_DOG_COMMENTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbl_Oz_DR_DOG_OWNER_COMMENTS]
GO
SELECT DISTINCT
person.p_person_id AS cv_dog_owner_id,
person.p_name,
person.p_forenames, dg_o_text_hdr.oth_owner_no,
z_temp_dogowner_text.Text_ID,
z_temp_dogowner_text.Text_IN,
SyfirLegacyLink__2007_06_12.SyfirAnybodyID AS origen_dog_owner_id
INTO tbl_Oz_DR_DOG_OWNER_COMMENTS
FROM SyfirLegacyLink__2007_06_12 INNER JOIN
person ON SyfirLegacyLink__2007_06_12.SourceID = person.p_person_id LEFT OUTER JOIN
dg_o_text_hdr INNER JOIN
dg_owner INNER JOIN
occupancy ON dg_owner.ow_occupancy = occupancy.o_serial_no ON dg_o_text_hdr.oth_owner_no = dg_owner.ow_serial INNER JOIN
z_temp_dogowner_text ON dg_o_text_hdr.oth_text_id = z_temp_dogowner_text.Text_ID ON person.p_person_id = occupancy.o_person_id
WHERE (SyfirLegacyLink__2007_06_12.SourceTableName = 'persn_other') AND (occupancy.o_role = 'DG')
GO
SELECT * FROM tbl_Oz_DR_DOG_OWNER_COMMENTS
GO
concatenate the text_id and text per dog into one line seperating them by **** using the following statement:
----------------------------------------------------------------
-- Clear Everything First
----------------------------------------------------------------
DELETE FROM dbo.tbl_Oz_DR_DOG_OWNER_COMMENTS_A
----------------------------------------------------------------
-- Create the cursor to the SELECT statement
----------------------------------------------------------------
DECLARE @PreviousReg int
DECLARE @CurrentReg int
DECLARE @cv_dog_owner_id int
DECLARE @p_name varchar (50)
DECLARE @Text_ID varchar (250)
DECLARE @Text_IN varchar (8000)
DECLARE @origen_dog_owner_id varchar (250)
---------
DECLARE Dr_OwnwerCommentsSearchCursor CURSOR FOR
SELECT
cv_dog_owner_id,
p_name,
Text_ID,
Text_IN,
origen_dog_owner_id
FROM tbl_Oz_DR_DOG_OWNER_COMMENTS
ORDER BY cv_dog_owner_id ASC
--------------------------------------------------------------
--Openand fetch the data from the cursor
--------------------------------------------------------------
OPEN Dr_OwnwerCommentsSearchCursor
FETCH NEXT FROM Dr_OwnwerCommentsSearchCursor INTO
@cv_dog_owner_id ,
@p_name,
@Text_ID,
@Text_IN,
@origen_dog_owner_id
WHILE @@FETCH_STATUS = 0
BEGIN
--check for nulls
SET @CurrentReg = @cv_dog_owner_id
IF @CurrentReg = @PreviousReg
BEGIN
UPDATE dbo.tbl_Oz_DR_DOG_OWNER_COMMENTS_A
SET
cv_dog_owner_id = @cv_dog_owner_id,
p_name = @p_name,
Text_ID = Text_ID+'****'+CAST(@Text_ID AS VARCHAR),
Text_IN = LTRIM(RTRIM(Text_IN))+'****'+CAST(@Text_IN AS VARCHAR),
origen_dog_owner_id = @origen_dog_owner_id
WHERE cv_dog_owner_id = @CurrentReg
-- get the next record from the cursor
FETCH NEXT FROM Dr_OwnwerCommentsSearchCursor INTO
@cv_dog_owner_id ,
@p_name,
@Text_ID,
@Text_IN,
@origen_dog_owner_id
SET @PreviousReg = @CurrentReg
END
ELSE
-- do an insert (create new record)
BEGIN
--set the previous reg and insert the data
SET @PreviousReg = @CurrentReg
INSERT INTO dbo.tbl_Oz_DR_DOG_OWNER_COMMENTS_A
(
cv_dog_owner_id,
p_name,
Text_ID,
Text_IN,
origen_dog_owner_id
)
VALUES
(
@cv_dog_owner_id ,
@p_name,
@Text_ID,
@Text_IN,
@origen_dog_owner_id
)
-- get the next record from the cursor
FETCH NEXT FROM Dr_OwnwerCommentsSearchCursor INTO
@cv_dog_owner_id ,
@p_name,
@Text_ID,
@Text_IN,
@origen_dog_owner_id
END
END
----------------------------------------------------------------------
--Close and deallocate the cursor
----------------------------------------------------------------------
CLOSE Dr_OwnwerCommentsSearchCursor
DEALLOCATE Dr_OwnwerCommentsSearchCursor
The problems is that the second statement is cutting off the text as in the below example:
cv_dog_owner_id,p_name,p_forenames,oth_owner_no,Te xt_ID,Text_IN,origen_dog_owner_id
453,NEWMAN ,CATHERINE MARY ,25,19,10/10/95 2389902 SID GORDON 53 PROSPECT TCE PUKEKOHE S/B/T & BOXER Resolved. COA EX ROUND PSTH 113/725,122900
453,NEWMAN ,CATHERINE MARY ,25,11226,MRS NEWMAN RANG 09/02/2000 TO SAY THEY ARE BUILDING IN PREMILA DRIVE PUKEKOHE AND HAVE NOT BEEN ALLOTTED A STREET NUMBER YET BUT THEIR MAILING ADDRESS FOR THE NEXT SIX MONTHS OR SO WILL BE TO P O BOX 864 PUKEKOHE. THEIR NEW PROPERTY NO. IS 38706/063.12. LO,122900
(2 row(s) affected)
cv_dog_owner_id,p_name,Text_ID,Text_IN,origen_dog_ owner_id
453,NEWMAN ,19****11226,10/10/95 2389902 SID GORDON 53 PROSPECT TCE PUKEKOHE S/B/T & BOXER Resolved. COA EX ROUND PSTH 113/725****MRS NEWMAN RANG 09/02/2000 TO ,122900
(1 row(s) affected)
Any help would be much appriciated
Thanks
Greg
|
