Concatenating a one-to-many using varchar data 2

[MsHart2u]

I hate to be redundant in asking for help on a problem already solved and I am attempting to use either of the recommended solutions listed in FAQ183-2146 (Concatenating a one-to-many relationship into a single column) with a twist.

Let me say this faq was tremendously helpful, enlightening has me close to my finish line; however, I am still having problems with my situation because I am wanting to concatenate varchar fields versus int fields. I've tried both solutions and run into errors each time.

********************************
----------Solution 1: (bperry)-----------
MY CODE CHANGES:

CREATE TABLE #TempTable (
txtISBN varchar(10),
txtAuthors varchar(20) ,
)

-- Create a forward-only cursor to collect the data
declare myCursor INSENSITIVE CURSOR FOR
Select dblISBN, txtAuthor
From tmpROSI
Open myCursor

-- Create the local variables needed
declare @currKey varchar(10),
declare @prevKey varchar(10),
declare @currLine varchar(20)
declare @BigLine varchar(200)
.
.
.

*********MY ERROR R E S U L T S ***********
(1 row(s) affected)
.
.
.
Server: Msg 245, Level 16, State 1, Line 34
Syntax error converting the varchar value '186450207X' to a column of data type int.

********************************
----------Solution 2tbroadbent)-----------
---------------------
MY CODE CHANGES:

Create table #tmp(
dblISBN varchar(10),(,
Authors varchar(8000)(
)

Insert #tmp
Select Distinct dblISBN,''
From tmpROSI

Declare @ln int, @mln int
Select @ln=1, @mln= Count (txtAuthor)
From tmpROSI
.
.
.
*********MY ERROR R E S U L T S ***********
(49 row(s) affected)
.
.
.

Server: Msg 245, Level 16, State 1, Line 18
Syntax error converting the varchar value 'Hull, Rikki' to a column of data type int.

Thanks in advance for your help. You all are really the best solution source.


Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[nigelrivett]

You aren't showing the code which is causing the error.
All I can suggest is that int has higher precedence than char so

@s + @i will give that error.
To concatenate
@s + convert(varchar(20),@i)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

Your datatype is set to an Integer(Int) where it should be set to a VarChar. Both error messages are telling you that you are trying to stick text into an Int field.

Server: Msg 245, Level 16, State 1, Line 34
Syntax error converting the varchar value '186450207X' to a column of data type int

AND ...

Server: Msg 245, Level 16, State 1, Line 18
Syntax error converting the varchar value 'Hull, Rikki' to a column of data type int.



Thanks

J. Kusch

 

[MsHart2u]

... I was attempting to be frugal on space... but here is my code in its entirety for the 1st method attempted. There is no int column defined in it, yet I get the aforementioned error message.

CREATE TABLE #TempTable (
txtISBN varchar(20),
txtAuthor varchar(200),
)

-- Create a forward-only cursor to collect the data
declare myCursor INSENSITIVE CURSOR FOR
Select dblISBN, txtAuthor
From tmpROSI
Open myCursor

-- Create the local variables needed
declare @currKey varchar(10)
declare @prevKey varchar(10)
declare @currLine varchar(20)
declare @BigLine varchar(200)
set @currLine = ''
set @bigLine = ''
set @prevKey = 0
set @prevKey = -1 -- Prevent Insert on 1st record

-- Fetch the cursor
Fetch myCursor into @currKey, @currLine

While @@fetch_status = 0
BEGIN
If @currKey <> @prevKey
If @prevKey != -1
Begin
INSERT #TempTable
VALUES(@prevKey, @bigLine)
SET @bigLine = ''
End
set @prevKey = @currKey
set @bigLine = LTRIM(@bigLine + ' ') + RTRIM(@currLine)
set
Fetch myCursor into @currKey, @currLine
END

-- Do final Insert for the last record
INSERT #TempTable VALUES(@prevKey, @bigLine)

-- Release cursor
close myCursor
deallocate myCursor


Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[MsHart2u]

Nigelrivett, et.al,

It seems to break at this line after the BEGIN statement:
...
If @prevKey != -1
...

Thanks again.
Kathy

http://www.lonelyplanet.com

 

[SQLSister]

BTW why are you doing this in a cursor anyway? No need for a cusor here and they are slow.

Try instead:

Insert into #TempTable
Select dblISBN, txtAuthor
    From tmpROSI {/code]

 

[MsHart2u]

Sister,
I don't think inserting into a tmp table is what is want to do off the bat... (that would essentially be a copy, no?)
A snippet of my original data looks like this...

txtISBN txtAuthor tfPrimary
0864424256 Delacy, Richard 0
0864424361 Masters, Jim 0
086442535X King, John 1
086442535X King, John 0
086442535X Mayhew, Bradley 0
086442535X Mock, John 0
086442616X Abe, Yoshi 0


I need it to build into this...
txtISBN txtAuthors
0864424256 Delacy, Richard
0864424361 Masters, Jim
086442535X King, John; Mayhew, Bradley; Mock, John
086442616X Abe, Yoshi

Hopefully this visually clarifies my dilemma. (Incidentally, I changed the field name on tmpROSI to be txtISBN from dblISBN, although the original source isn't.

JayKusch, I understand the point that it is trying to place text into a numeric field and I understand Nigelrivett's point about typecasting; which field should I attempt to CONVERT. I am wanting to concatenate the authors, the error is speaking of the ISBN.
(I may have transposed the error messages originally, but the following is for sure as I aborted my attempts with the 2nd method.)

Here is my latest code:

CREATE TABLE #TempTable (
  txtISBN varchar(10),
  txtAuthor   varchar(200),
)

-- Create a forward-only cursor to collect the data
declare myCursor INSENSITIVE CURSOR FOR
  Select txtISBN, txtAuthor, tfPrimaryAuthor
    From tmpROSI 
	order by txtISBN,tfPrimaryAuthor DESC, txtAuthor
Open myCursor
-- Create the local variables
declare @currKey varchar(10)
declare @prevKey varchar(10)
declare @primary bit
declare @currLine varchar(20)
declare @BigLine varchar(200)
set @currLine = ''
set @bigLine = ''
set @prevKey = 0
set @prevKey = -1  -- Prevent Insert on 1st record

Fetch myCursor into @currKey, @currLine, @primary

While @@fetch_status = 0
BEGIN
  If @currKey <> @prevKey
     If @prevKey != -1
         Begin
           INSERT #TempTable
               VALUES(convert(varchar(10),@prevKey), convert(varchar(200),@bigLine))
           SET @bigLine = ''
         End
  set @prevKey = @currKey
  set @bigLine = LTRIM(@bigLine + ' ') + RTRIM(@currLine)
  set
  Fetch myCursor into @currKey, @currLine, @primary
END

INSERT #TempTable VALUES(@prevKey, @bigLine, @primary)

-- Release cursor
close myCursor
deallocate myCursor
-- Report the temporary table
select * From  #TempTable

drop table #TempTable
go

Here is the error msg:
Server: Msg 245, Level 16, State 1, Line 35
Syntax error converting the varchar value '086442535X' to a column of data type int.

Here is the line referenced by the message:
If @prevKey != -1

OUTPUT DATA (thus far) Looks like this:
0864424256 Delacy, Richard
0864424361 Masters, Jim

Thank you and bless you for your persistence to teach and your patience with my ability to grasp.

Kathy

 

[SQLBill]

Here's a problem that I see:

If @currKey <> @prevKey
     If @prevKey != -1
         Begin
           INSERT #TempTable
               VALUES[b](convert(varchar(10),@prevKey), convert(varchar(200),@bigLine))[/b]
           SET @bigLine = ''
         End
  set @prevKey = @currKey
  set @bigLine = LTRIM(@bigLine + ' ') + RTRIM(@currLine)
  set
  Fetch myCursor into @currKey, @currLine, @primary
END

INSERT #TempTable VALUES[b](@prevKey, @bigLine,[/b] @primary)

Notice the two INSERT lines....one you CONVERT @prevKey and @bigLine to VARCHAR and one you don't. You can't have it both ways...either the #TempTable columns take VARCHAR (using the CONVERT) or they take INT (not using CONVERT).

-SQLBill

 

[SQLBill]

OOOPPPS, while what I pointed out IS an error...it's not the one you are getting the message for.

I'll bet this: 086442535X is @prevKey. Am I correct?

Here's the problem....@prevKey is a VARCHAR (especially since it has the X in it). But 1 is an INT so...

This:

If @prevKey != -1

Reads as:

If a VARCHAR input != an INT input

and the two can't be compared. What is the format of @prevKey? Is it always with a letter at the end?

If so you could do:

IF CONVERT(INT, (SUBSTRING(@prevKey, 1, (LEN(@prevKey)-1)))) != 1

-SQLBill

 

[MsHart2u]

My code breaks before this opoint. But, thanks for the cleanup suggestion... I changed it to be consistent. I also corrected my fetch line to be
Fetch myCursor into @currKey, @currLine




Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[MsHart2u]

I get it, I got it!!! Thank you SQLBill, Thank you Nigelrivett, Thank you all!!!

Since I declared @prevKey as string (varchar)
I needed to change the following area of code

set @prevKey = '-1'-- Prevent Insert on 1st record

Fetch myCursor into @currKey, @currLine, @primary
While @@fetch_status = 0
BEGIN
If @currKey <> @prevKey
if @prevKey!='-1'

Nothing more was needed. It was that simple (Oh sure now I say that...)

Thanks a million.

Kathy

http://www.lonelyplanet.com