February 2, 2016 at 11:27 am
I'm trying to debug some code that uses a PIVOT. When the source table has 9999 rows in it, it works fine. Any more than that and it fails with a 245 conversion error. I've already eliminated that the problem is data related by using different data. Always fails with 10000 rows in the source table.
The code is used in our in house mail merge application that builds letters, paragraph by paragraph depending on the contents of dbo.invoicetags. I'm thinking it's a limitation of SQL Server, and I've looked at Server and database properties without any solution. Any input would be appreciated.
Here's the code.
SELECT Cast(Tt.rowid AS VARCHAR(4)) AS RowId,
lettertemplates.templateid,
Tt.letter_version,
1 AS level,
Replace(Replace(Replace(Replace(Replace(Replace(Replace(
Replace(
Replace(
Replace(
letter_template,
'<<Paragraph1>>'
,
Isnull(para1, ''
)),
'<<Paragraph2>>'
, Isnull(para2,
'')),
'<<Paragraph3>>'
,
Isnull(
para3, ''
)),
'<<Paragraph4>>'
,
Isnull(
para4, ''
)),
'<<Paragraph5>>'
,
Isnull(para5, '')),
'<<Paragraph6>>'
,
Isnull(para6, '')),
'<<Paragraph7>>',
Isnull(para7, '')),
'<<Paragraph8>>',
Isnull(
para8, '')), '<<Paragraph9>>',
Isnull
(
para9, '')
), '<<Paragraph10>>', Isnull(para10, '')) LetterBody,
lettertemplates.templatedescription
FROM dbo.invoicetags Tt
JOIN dbo.lettertemplates
ON lettertemplates.letterid = Tt.letter_version
LEFT JOIN (SELECT Cast(rowid AS VARCHAR(4)) AS RowId,
letterid,
level,
Isnull([1], '') AS Para1,
Isnull([2], '') AS Para2,
Isnull([3], '') AS Para3,
Isnull([4], '') AS Para4,
Isnull([5], '') AS Para5,
Isnull([6], '') AS Para6,
Isnull([7], '') AS Para7,
Isnull([8], '') AS Para8,
Isnull([9], '') AS Para9,
Isnull([10], '') AS Para10,
Isnull([11], '') AS Para11,
Isnull([12], '') AS Para12
FROM (SELECT Cast(T.rowid AS VARCHAR(4)) AS RowId,
[level],
[letterid],
[paragraphseqno],
[paragraphbody]
FROM [dbo].[letterparagraphs] P,
dbo.invoicetags T
WHERE ( P.letterid = T.letter_version )
AND ( P.active = 1 )) dta
PIVOT (Max(paragraphbody)
FOR [paragraphseqno] IN ( [1],
[2],
[3],
[4], http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12])) AS pvt)
Paragraphs
ON Paragraphs.letterid = lettertemplates.letterid
AND Tt.rowid = Paragraphs.rowid
AND Paragraphs.level = 1
February 2, 2016 at 11:31 am
10,000 not 1,000.
February 2, 2016 at 12:07 pm
I'm going to go out on a limb and guess this is the error you're getting?
Conversion failed when converting the varchar value '*' to data type int.
If so, then most likely the problem is that RowId in invoicetags is an integer, combined with all the converting of RowId to varchar(4) in the subqueries, which is large enough to convert a 4 digit int, but not a 5 digit int (like 10000).
If you try to cast a 5 digit int to varchar(4), it'll become a '*', and that would then fail to convert to an int when it had to convert back to join to invoicetables (int has higher data type precedence).
You could just convert to a larger string, or better yet, if the RowIds are integers to begin with, don't convert them.
An easy way to illustrate this problem is below:
CREATE TABLE #tags (RowId int);
CREATE TABLE #paragraphs (RowId int);
INSERT INTO #tags VALUES (10000);
INSERT INTO #paragraphs VALUES (10000);
SELECT * FROM #tags INNER JOIN (SELECT CAST(rowid as varchar(4)) as RowID
FROM #paragraphs) p ON #tags.RowId=p.RowID;
DROP TABLE #paragraphs,#tags;
Cheers!
EDIT: I originally missed a line of the script while copy/pasting. Fixed that.
February 2, 2016 at 12:30 pm
Why are you converting RowID to strings? You're only adding work to the server with no real purpose.
Try this to see if it works correctly.
SELECT Tt.rowid,
lt.templateid,
Tt.letter_version,
1 AS level,
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace( letter_template
, '<<Paragraph1>>' , Isnull(para1, '' ))
, '<<Paragraph2>>' , Isnull(para2, ''))
, '<<Paragraph3>>' , Isnull( para3, '' ))
, '<<Paragraph4>>' , Isnull( para4, '' ))
, '<<Paragraph5>>' , Isnull(para5, ''))
, '<<Paragraph6>>' , Isnull(para6, ''))
, '<<Paragraph7>>', Isnull(para7, ''))
, '<<Paragraph8>>', Isnull( para8, ''))
, '<<Paragraph9>>', Isnull ( para9, ''))
, '<<Paragraph10>>', Isnull(para10, '')) LetterBody,
lt.templatedescription
FROM dbo.invoicetags Tt
JOIN dbo.lettertemplates lt ON lt.letterid = Tt.letter_version
LEFT JOIN (SELECT rowid,
letterid,
MAX(CASE WHEN [paragraphseqno] = 1 THEN paragraphbody ELSE '' END) AS Para1,
MAX(CASE WHEN [paragraphseqno] = 2 THEN paragraphbody ELSE '' END) AS Para2,
MAX(CASE WHEN [paragraphseqno] = 3 THEN paragraphbody ELSE '' END) AS Para3,
MAX(CASE WHEN [paragraphseqno] = 4 THEN paragraphbody ELSE '' END) AS Para4,
MAX(CASE WHEN [paragraphseqno] = 5 THEN paragraphbody ELSE '' END) AS Para5,
MAX(CASE WHEN [paragraphseqno] = 6 THEN paragraphbody ELSE '' END) AS Para6,
MAX(CASE WHEN [paragraphseqno] = 7 THEN paragraphbody ELSE '' END) AS Para7,
MAX(CASE WHEN [paragraphseqno] = 8 THEN paragraphbody ELSE '' END) AS Para8,
MAX(CASE WHEN [paragraphseqno] = 9 THEN paragraphbody ELSE '' END) AS Para9,
MAX(CASE WHEN [paragraphseqno] = 10 THEN paragraphbody ELSE '' END) AS Para10,
MAX(CASE WHEN [paragraphseqno] = 11 THEN paragraphbody ELSE '' END) AS Para11,
MAX(CASE WHEN [paragraphseqno] = 12 THEN paragraphbody ELSE '' END) AS Para12
FROM [dbo].[letterparagraphs] P
JOIN dbo.invoicetags T ON P.letterid = T.letter_version
WHERE P.active = 1
AND level = 1
GROUP BY rowid,
letterid) pr ON pr.letterid = lt.letterid
AND Tt.rowid = pr.rowid;
Please, use 2 part names for your columns.
If this doesn't work, try checking all the comparisons to check that you're not comparing integers to strings. I believe that Jacob is right in his guess, but there could be more problems as we can't see the definition of your tables, nor we can reproduce the error.
February 2, 2016 at 12:33 pm
That was it. Thanks for catching that.
February 2, 2016 at 12:35 pm
Casting the rowId to something larger than 4, fixed the problem. Thanks for the help.
February 2, 2016 at 12:40 pm
rbrescia 21348 (2/2/2016)
Casting the rowId to something larger than 4, fixed the problem. Thanks for the help.
I'm glad we could help!
Having said that, now that cause of the error has been identified I have to stress again along with Luis that it would be best not to convert the RowId in the first place.
Cheers!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
