Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 29th, 2007, 04:00 PM
Authorized User
 
Join Date: Jul 2006
Location: Pukekohe, , New Zealand.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

 
Old September 7th, 2007, 09:49 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

No... nothing is being cut off... it's just not being displayed in Query analyzer... the default display width for QA is only 255 characters... in QA, go to Tools, Options, Results and change the column width to 8000.

--Jeff Moden




Similar Threads
Thread Thread Starter Forum Replies Last Post
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
In Cursor set and execute statement related proble param99 SQL Server 2000 0 December 15th, 2006 02:55 AM
Regarding Cursor param99 SQL Server 2000 1 September 8th, 2006 10:03 AM
Regarding Cursor param99 SQL Language 0 September 8th, 2006 03:56 AM
cursor trinnie SQL Server 2000 0 August 6th, 2006 09:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.