Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 24th, 2007, 04:06 PM
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
  #2 (permalink)  
Old July 2nd, 2007, 07:19 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old July 2nd, 2007, 11:20 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

Hi there, Greg...

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

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 , 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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
text on the same line Adam H-W CSS Cascading Style Sheets 2 February 19th, 2007 04:10 PM
Display 2 fields as one line in the combobox osemollie VB Databases Basics 2 May 17th, 2006 10:22 AM
Problem with labels and entry fields on same line ectounix CSS Cascading Style Sheets 3 April 3rd, 2006 12:35 PM
How to retrieve last selected text line in text bo garetho General .NET 1 May 3rd, 2005 09:17 PM



All times are GMT -4. The time now is 12:54 AM.


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