I am struggling for a few days with this issue and I can't figure out how can I fix it.
I would like to group by
my table on values 1
,2
,3
,4
,5
so I have created a temporary table with this values.
Now I have to INNER JOIN
this table with other tables on a.value = #myTempTable.num
.
BUT a.value
is ntext
so I need to CONVERT
it what I actually did, but I am getting an error:
Conversion failed when converting the varchar value 'simple, ' to data
type int. (on line 7)
Create table #myTempTable
(
num int
)
insert into #myTempTable (num) values (1),(2),(3),(4),(5)
SELECT a.name, CONVERT(INT, CONVERT(VARCHAR(12), 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 CONVERT(INT, CONVERT(VARCHAR(12), a.value)) = #myTempTable.num
GROUP BY a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) ORDER BY a.name
drop table #myTempTable
I am not getting this error without the last INNER JOIN
INNER JOIN #myTempTable on CONVERT(INT, CONVERT(VARCHAR(12), a.value))
= #myTempTable.num
Could you help me please? I am desperate.
Thanks.
Best Answer
In order to avoid such error you could use
CASE
+ISNUMERIC
to handle scenarios when you cannot convert to int.Change
To
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