p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   concatenationg text fields into one line (http://p2p.wrox.com/showthread.php?t=59392)

gregalb June 24th, 2007 04:06 PM

concatenationg text fields into one line
 
Hi There

I want to concatenate some free text into one line. What it is, is that for every property id that i have there is a text field per line so that means that a property can have more that one text line. I would like to extract this into one line an remove any blank spaces.

The following script is what I am using but I keep getting this error message. Has anyon got any ideas as to what is wrong in my script?

ERROR:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 512, Level 16, State 1, Procedure udf_Rates_Trans_GetLine, Line 6

SCRIPT

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--***********************************************
--** Create required functions
--***********************************************

--Returns the number of text lines for a note
CREATE FUNCTION udf_Rates_Trans_GetNumLines (@var2 int)
RETURNS int
AS
BEGIN
DECLARE @var1 int
Set @var1 = (SELECT max(pt_seq) FROM pt_text WHERE pt_text.pt_text_id = @var2)
return (@var1)
END
GO
--Retrieves the text contained within a particular line
CREATE FUNCTION udf_Rates_Trans_GetLine (@var1 int, @var2 int)
RETURNS varchar(100)
AS
BEGIN
   RETURN( SELECT pt_text.pt_text
       FROM pt_text
       WHERE pt_text.pt_text_id = @var1
       AND pt_text.pt_seq = @var2)
END
GO
--Stripes out the duplicate blanks that can occur within a text line
CREATE FUNCTION udf_StripBlanks (@var1 varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
   RETURN(replace(replace(replace(@var1, ' ', ' ' + '¬'), '¬' + ' ', ''), '¬', '') )
END
GO
--Gets the next unique AccID
CREATE FUNCTION udf_Rates_Trans_GetNextID (@var1 int)
RETURNS int
AS
BEGIN
   DECLARE @var3 int
   SELECT @var3 = min(pt_text_ID)
   FROM pt_text
   WHERE pt_text.pt_text_ID > @var1
   IF ISNULL(@var3,0) = 0
   BEGIN
     SET @var3 = 0
   END
Return(@var3)

END
GO
--***********************************************
--** Create temp table
--***********************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp_RT_Comments]'))
    drop table [dbo].[temp_RT_Comments]
GO

CREATE TABLE [dbo].[temp_RT_Comments] (
     [text_ID] INT NULL,
    [text_ln] varchar(1000) NULL
)

--***************************************
-- Declare Vars
--***************************************
DECLARE @minID INT,
@maxID INT,
@seqCnt INT,
@totalID INT,
@seqMax INT,
@currID INT,
@currSeq INT,
@currTxt varchar(60),
@ln2write varchar(1000)

--***************************************
-- Initialise Vars
--***************************************

SELECT @minID = min(pt_text_ID),
@maxID = max(pt_text_ID),
@totalID = count(pt_text_ID)
FROM
pt_text
SELECT @seqCnt = 1
SELECT @currSeq = 1
SELECT @ln2write = ''
SELECT @currID = @minID


--***********************************************
--** Main
--***********************************************

WHILE (@currID <= @maxID)
BEGIN
   SET @seqMax = [dbo].[udf_Rates_Trans_GetNumLines](@currID)
   WHILE @currSeq <= @seqMax --WHILE LOOP to join all text lines
      BEGIN
     SELECT @ln2write = @ln2write + [dbo].[udf_Rates_Trans_GetLine](@currID, @currSeq)
     SELECT @currSeq = @currSeq + 1
      END
      SET @ln2write = dbo.udf_StripBlanks(@ln2write)
      INSERT [dbo].[temp_RT_Comments] VALUES (@currID,@ln2write) --WRITE data to temp table select * from [dbo].[udf_WB_Trans_WB1]
      SELECT @ln2write = ''

   --Get next ID
   SELECT @currID = [dbo].[udf_Rates_Trans_GetNextID](@currID)
   SELECT @currSeq = 1
   IF @currID = 0
    BREAK
END

-------------------------------------------------------------
--clean up
-------------------------------------------------------------

GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Rates_Trans_GetNumLines]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Rates_Trans_GetNumLines]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Rates_Trans_GetLine]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Rates_Trans_GetLine]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_StripBlanks]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_StripBlanks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Rates_Trans_GetNextID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Rates_Trans_GetNextID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[temp_RT_Comments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [temp_RT_Comments]
GO

Thanks,

Greg


rstelma July 2nd, 2007 07:19 PM

Hi,

I can never figure out which line they're talking about with those error messages.

Can you isolate the parts of this script and run them with hard-coded real numbers. Once you know where its freaking out, you're halfway home. You probably know this already though.

Anyway, I've gotten that error quite often when I'm not paying attention and I do have a subquery that returns more than one result.

I know this might not be much help but I saw no one had made peep.

RS


Jeff Moden July 2nd, 2007 11:20 PM

Hi there, Greg...

Yes, I have some ideas as to what is wrong with your script... my idea is to rewrite what you have [:0] for much greater performance. [:p]

First, I'm making the assumption that Pt_Text_ID is not unique in the Pt_Text table and that each Pt_Text_ID could have one or more rows as identified by the Pt_Seq column... if that's not true [xx(], then post back and tell me what you're really trying to do and we'll fix this together :)

Second, your blank stripping function is absolutely ingenious... but it has two major flaws and a bit of a performance problem...

1. Because you initially double the number of characters occupied by blanks, you've limited the function to no more than 4,000 characters meaning that you could never do a full 8,000 character VARCHAR.

2. When the 1,000 character limit is breeched in your current function, truncation of the string occurs without error and without warning which could lead to some pretty serious errors downstream.

3. The method you used, although very clever, is actually slower than other methods.

For example, on 10,000 rows with data that looks like 'X'+SPACE(998)+'Y' (the limit of your function), your function takes about 10.1 seconds to execute. Although the following code is butt ugly and not clever at all, it only takes 5.6 seconds to accomplish the same thing AND it's capable of doing a full 8000 character varchar AND it deletes any leading or trailing spaces to boot!

Code:

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

...by the way... why 13 REPLACEs? Because 2^13 > 8000.

OK, moving on... there is no need for the following functions...

Code:

udf_Rates_Trans_GetNumLines
udf_Rates_Trans_GetLine
udf_Rates_Trans_GetNextID
udf_WB_Trans_WB1

... nor is there any need for a temp table or a While loop to do this concatenation. Think set based...

...The following function, when used with the code that follows it, will return the result-set you desire without any of that other stuff...

Code:

CREATE FUNCTION dbo.udf_AssemblePT
        (@Pt_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(Pt_Text)
         FROM Pt_Text
         WHERE Pt_Text_ID = @Pt_Text_ID
         ORDER BY Pt_Seq

        --===== Return the concatenated parts for this Pt_Text_ID
         RETURN @Return
    END



Once you have that function, then your code simply looks like this...

Code:

SELECT DISTINCT
        Pt_Text_ID                    AS Text_ID,
        dbo.udf_AssemblePT(Pt_Text_ID) AS Text_IN
FROM Pt_Text
ORDER BY Pt_Text_ID

Because this is mostly set based, it will run circles around your While loop and it's a whole lot easier to maintain/modify...

Admittedly, I've not tested this exact code, but I've written similar code a thousand times. So, I may have a bug in the code (not intentionally, of course), but it should be easy to fix if you find one.

Any questions?

--Jeff Moden


All times are GMT -4. The time now is 12:05 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.