Conversion failed when converting the varchar value '.' to data type int

Question

I have a query as below:

SELECT activity_code, 
       activity_name, 
       Count(*) 
       Total_Response, 
       yearnumber, 
       Cast(Avg(Cast(Isnull([response], 0) AS NUMERIC)) AS DECIMAL(10, 2)) 
       [Ratings] 
FROM   (SELECT code         [Activity_Code], 
               activityname [Activity_Name], 
               enddt.yearnumber, 
               CASE 
                 WHEN Isnumeric(asmt.userresponse) = 1 THEN asmt.userresponse 
                 ELSE 0 
               END          [Response] 
        FROM   (SELECT userid, 
                       questionid, 
                       activityid, 
                       attemptenddateid, 
                       CASE 
                         WHEN Isnumeric(userresponse) = 1 THEN userresponse 
                         ELSE '0' 
                       END AS UserResponse 
                FROM   factassessment) asmt 
               INNER JOIN dimuser usr 
                       ON usr.id = asmt.userid 
               INNER JOIN (SELECT 
                                               id, 
                                  Cast(questionidentifier AS VARCHAR(1000)) AS 
                                               QuestionIdentifier 
                           FROM   dimquestion) ques 
                       ON ques.id = asmt.questionid 
               INNER JOIN dimactivity act 
                       ON act.id = asmt.activityid 
               INNER JOIN dimdate enddt 
                       ON enddt.dateid = asmt.attemptenddateid 
        WHERE  ( act.code LIKE 'U-%' 
                  OR act.code LIKE 'CC-%' ) 
               AND usr.empcntry <> 'administrator' 
               AND enddt.yearnumber IN ( 2015, 2016, 2017, 2018, 
                                         2019, 2020, 2021, 2021, 2022 ) 
               AND ques.questionidentifier = 'course_content' 
               AND asmt.userresponse NOT IN ( '0', '-1' )) tab 
GROUP  BY activity_code, 
          activity_name, 
          yearnumber 
ORDER  BY activity_code 

On executing the above code, I am getting the below error:

[SQLServer]Conversion failed when converting the varchar value '.' to data type int.

I tried fixing it as below, but still getting the same error:

SELECT activity_code, 
       activity_name, 
       Count(*) 
       Total_Response, 
       yearnumber, 
       Cast(Avg(Cast(Isnull([response], 0) AS NUMERIC)) AS DECIMAL(10, 2)) 
       [Ratings] 
FROM   (SELECT code         [Activity_Code], 
               activityname [Activity_Name], 
               enddt.yearnumber, 
               CASE 
                 WHEN Isnumeric(asmt.userresponse + 'e0') = 1 THEN 
                 asmt.userresponse 
                 ELSE 0 
               END          [Response] 
        FROM   (SELECT userid, 
                       questionid, 
                       activityid, 
                       attemptenddateid, 
                       CASE 
                         WHEN Isnumeric(userresponse + 'e0') = 1 THEN 
                         userresponse 
                         ELSE '0' 
                       END AS UserResponse 
                FROM   factassessment) asmt 
               INNER JOIN dimuser usr 
                       ON usr.id = asmt.userid 
               INNER JOIN (SELECT 
                                               id, 
                                  Cast(questionidentifier AS VARCHAR(1000)) AS 
                                               QuestionIdentifier 
                           FROM   dimquestion) ques 
                       ON ques.id = asmt.questionid 
               INNER JOIN dimactivity act 
                       ON act.id = asmt.activityid 
               INNER JOIN dimdate enddt 
                       ON enddt.dateid = asmt.attemptenddateid 
        WHERE  ( act.code LIKE 'U-%' 
                  OR act.code LIKE 'CC-%' ) 
               AND usr.empcntry <> 'administrator' 
               AND enddt.yearnumber IN ( 2015, 2016, 2017, 2018, 
                                         2019, 2020, 2021, 2021, 2022 ) 
               AND ques.questionidentifier = 'course_content' 
               AND asmt.userresponse NOT IN ( '0', '-1' )) tab 
GROUP  BY activity_code, 
          activity_name, 
          yearnumber 
ORDER  BY activity_code 

I am still getting the same error. Help to resolve this conversion error.

Answer 1

If I understand this question correctly, the reason for this error can be simplified to the following statement, which returns Conversion failed when converting the varchar value '.' to data type int. error:

SELECT CASE WHEN ISNUMERIC(asmt.UserResponse) = 1 THEN asmt.UserResponse else 0 END [Response]
FROM (
    SELECT CASE WHEN ISNUMERIC('.') = 1 THEN '.' ELSE '0' END AS UserResponse
) asmt

It's good to consider the following:

• The result from ISNUMERIC('.') is 1 and the result from the inner CASE is the text .. But, the outer CASE fails, because the return type from the CASE statement is the highest precedence type from the set of types in result expressions, so in this case . is implicitly converted to int.
• Do not trust the ISNUMERIC() function. Use TRY_CONVERT() instead

You may try to use the next approach to solve this error:

SELECT 
   CASE 
       WHEN TRY_CONVERT(int, asmt.UserResponse) IS NOT NULL THEN TRY_CONVERT(int, asmt.UserResponse) 
       ELSE 0 
   END [Response]
FROM (
    SELECT 
       CASE 
          WHEN TRY_CONVERT(int, '.') IS NOT NULL THEN TRY_CONVERT(int, '.') 
          ELSE 0 
       END AS UserResponse
) asmt