Conversion failed when converting the varchar value 'simple, ' to data type int
Given that you're only converting to int
s to then perform a comparison, I'd just switch the table definition around to using varchar
also:
Create table #myTempTable
(
num varchar(12)
)
insert into #myTempTable (num) values (1),(2),(3),(4),(5)
and remove all of the attempted CONVERT
s from the rest of the query.
SELECT a.name, a.value AS value, COUNT(*) AS pocet
FROM
(SELECT item.name, value.value
FROM mdl_feedback AS feedback
INNER JOIN mdl_feedback_item AS item
ON feedback.id = item.feedback
INNER JOIN mdl_feedback_value AS value
ON item.id = value.item
WHERE item.typ = 'multichoicerated' AND item.feedback IN (43)
) AS a
INNER JOIN #myTempTable
on a.value = #myTempTable.num
GROUP BY a.name, a.value ORDER BY a.name
If you are converting a varchar to int make sure you do not have decimal places.
For example, if you are converting a varchar field with value (12345.0) to an integer then you get this conversion error. In my case I had all my fields with .0 as ending so I used the following statement to globally fix the problem.
CONVERT(int, replace(FIELD_NAME,'.0',''))
In order to avoid such error you could use CASE
+ ISNUMERIC
to handle scenarios when you cannot convert to int.
Change
CONVERT(INT, CONVERT(VARCHAR(12), a.value))
To
CONVERT(INT,
CASE
WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value)
ELSE 0 END)
Basically this is saying if you cannot convert me to int assign value of 0 (in my example)
Alternatively you can look at this article about creating a custom function that will check if a.value
is number: http://www.tek-tips.com/faqs.cfm?fid=6423