View Single Post
  #1 (permalink)  
Old June 24th, 2007, 04:06 PM
gregalb gregalb is offline
Authorized User
 
Join Date: Jul 2006
Location: Pukekohe, , New Zealand.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Reply With Quote