there is a field 'USER_7' of the table 'WORK_ORDER', said field is of type varchar(80). It stores numbers.
The problem with this is that the table will not be modified with the correct value type. And I have to run my queries on these.
I want to add the USER_7 field, for this I have to convert it to a numeric type. If the conversion is not given for X or Y reason then the exception throws a zero in the error row, but in the end it adds up the entire filtered column... with the numbers that could be converted and the zeros that were the rows that could not be converted.
SELECT CASE WHEN ISNUMERIC(USER_7) = 1 THEN SUM(cast(USER_7 as decimal(28, 20))) else 0 end DESPERDICIO
FROM [SHALER].[dbo].[WORK_ORDER]
where BASE_ID IN (
'OP-00001-16',
'OP-00007-16',
'OP-00016-16',
'OP-00020-16',
'OP-00025-16',
'OP-00030-16',
'OP-00036-16',
'OP-00040-16',
'OP-00044-16',
'OP-00047-16',
'OP-00047-16',
'OP-00049-16',
'OP-00050-16',
'OP-00055-16',
'OP-00059-16',
'OP-00061-16',
'OP-00067-16',
'OP-00069-16',
'OP-00074-16',
'OP-00075-16',
'OP-00076-16'
)