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

0
votes

I have sql like below. Home hints:

Results in K table (from WITH) gives number codes of specified range (ex.: 1-90).

The main select part works fine until I use 

sum(F._IsInIntersection) [CountIntersection]

It causes error

Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the varchar value 'ADMIN' to data type int.

If I comment this line, it is OK.

I do not know, why this error occurs the line with SUM() contains only int values. No ADMIN value (as mentioned in the error) exists anywhere...

Can anyone see any problems with the query?

;with K as ( 
select cast(c.Code as int) code
from rmCategory c, rmCategoryGroup cg, rmIdentification ic
where 1=1
  and c.CategoryGroupID=cg.ID
  and c.ID=ic.ID0
)

select 
F.STAV_AKT [Code]
, count(F.STAV_AKT) [Count] 
, sum(F._IsInIntersection) [CountIntersection]

from
(
select 
    f.*
    , case when f.KVALIF IN (select code from K) and f.KVALIF is not null then 1 else 0 end _IsInIntersection
from frmFormData_208629_MaMo2_IMPORT f, rmIdentification i

where 1=1
    and f.ID=i.id0
    and i.isValid=1
    and f.sysDAVKA=5

) F

group by F.STAV_AKT
order by F.STAV_AKT

I also tried CASTing parameter inside of sum(cast(F._IsInIntersection as int)) [CountIntersection] to be sure to use int values instead of the boolean ones, but no change.

2
sqlsql-server

2 Answers

1
votes

Martin, I'm suspecting that c.Code also contains the value 'ADMIN' in the part

cast(c.Code as int) code

It runs good without the SUM because it's part of a CTE and is not materialized when the column is not used.

If for example I run the following code example in Fiddle it runs fine, but if I also select the column Code it fails when trying to CAST 'ADMIN' to INT (Fiddle).

;WITH cte as
(
    select CAST(a AS INT) AS Code
            ,'Dummy' AS Column2
    FROM (
            SELECT 1 AS a
            UNION ALL SELECT 'a'
        ) t
)
SELECT Column2
    --,Code
FROM cte

So try to figure out what exactly the values for column Code are by using e.g.

SELECT DISTINCT Code FROM rmCategory
0
votes

What is the datatype of f.sysDAVKA?

Try running 

SELECT f.sysDAVKA 
FROM frmFormData_208629_MaMo2_IMPORT f 
WHERE f.sysDAVKA = 'ADMIN'

My gut feeling is you will find at least one row. If this is true, then changing your WHERE clause to

where 1=1
    and f.ID=i.id0
    and i.isValid=1
    and f.sysDAVKA=CAST(5 AS char(1))

should resolve the problem.