I'm getting that on this stored procedure:

it appears to be on this line: N'CAST((1.0 * icnt / NULLIF(tcnt, 0)
* 100) AS decimal
(6,
1)) AS perc

What do I change it to so I can get rid of the error?

SET NOCOUNT ON
Drop Table iClaimsYrPivot

DECLARE @pivot_cols NVARCHAR(MAX);
SELECT @pivot_cols =
STUFF((SELECT ',MAX(CASE WHEN year_month = ''' + year_month +
''' THEN perc END) AS [' + year_month + ']' +
',RANK() OVER(ORDER BY MAX(CASE WHEN year_month = '''

+
year_month +
''' THEN perc END)) AS [' + year_month + ' rank]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN icnt END) AS [' + year_month + ' icnt]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN tcnt END) AS [' + year_month + ' tcnt]'
FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9),
CAST(dowrdt AS DATETIME), 6), 6),
CONVERT(CHAR(7), CAST(dowrdt AS DATETIME),
126)
FROM iclaimsYear) AS T(year_month, sort)
ORDER BY sort
FOR XML PATH('')
), 1, 1, '');

DECLARE @pivot_query NVARCHAR(MAX);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +
N' ,region, area, dist, doc, rpt ' +
N' INTO iClaimsYrPivot ' +
N'FROM (SELECT sort, reg, region, area, dist, doc, rpt, icnt, tcnt,
dowrdt, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME),
6),
6) AS year_month, ' +
N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS
decimal
(6,
1)) AS perc ' +
N'FROM iClaimsYear) AS F ' +
N'GROUP BY sort, reg, area, dist, doc, rpt, region ' +
N'ORDER BY sort;';


EXEC(@pivot_query);

Am 31.08.2010 13:57, schrieb JJ297:
Maybe tcnt is NULL so the term becomes icnt / 0 ???

JJ297 (nc297 (AT) yahoo (DOT) com) writes:

Change decimal to float to see what values you get, and see if there is
anything suspicious.

When you the decimal data type, you should always specify precision and
scale explicitly, and not rely on defaults.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Aug 31, 8:19*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks I changed it to decimal(18,1) and that seemed to work. What do
you mean by this...

When you the decimal data type, you should always specify precision
and
scale explicitly, and not rely on defaults.

How would I do this?

You already do this with (18,1)!
I think he did not read this in your first post because of

Lutz

On Aug 31, 9:21*am, Lutz Uhlmann <n... (AT) invalid (DOT) invalid> wrote:
Okay thanks!