Posted by Hugo Kornelis on 01/13/06 00:10

On 12 Jan 2006 05:31:14 -0800, 2redline wrote:

>Had one correction to the last line and removed the t from sit to be
>si.
>
>Query Analyzer gives an error of
>"Server: Msg 529, Level 16, State 2, Line 1
>Explicit conversion from data type text to decimal is not allowed."
>
>This is the same error I got during previous attempts also.
>
>It makes me believe it is something to do with the field type. It is
>Text, 16, allow nulls

Hi 2redline,

Lyle's answer gives you the reason for the error.

You can get around this by first casting from text to varchar, then
casting that to decimal:

SELECT AVG(CAST(CAST(ra.answer_text AS varchar(40)) AS
decimal(5,2))) AS avg_correct
FROM dbo.sur_response_answer AS ra
INNER JOIN dbo.sur_subitem AS si
ON si.subitem_id = ra.subitem_id
INNER JOIN dbo.sur_response AS r
ON r.response_id = ra.response_id
WHERE r.completed_yn = 'Y'
AND sit.subitem_id = 478;

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]