Getting conversion error, for value that doesnt exist in database [closed]

Question

I am running this query:

SELECT 
    thirdpid
    , ea.Empid
    , ea.Empname
    , em.RsCode
    , em.CustomerName AS 'RS Name'
    , SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
    , SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
    , SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
    , SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
    , SUM(CASE WHEN ea.Reason IN 
        ('National Holiday','State Holiday','Weekly Holiday','Special Holiday') 
        THEN 1 
        ELSE 0 
        END) AS 'Official Holidays'
FROM 
    EmployeeAttendance_Backup ea
    , Customer em
    , Employee emp
WHERE 
    ea.markedFor >= '2012-08-26' 
    AND ea.markedFor <= '2012-09-25' 
    AND em.RSCode = ea.DistID 
    AND emp.EmployeeMasterID = ea.Empid
GROUP BY 
    ea.Empid
    , ea.Empname
    , ea.DistID
    , em.RsCode
    , em.CustomerName
    , emp.FirstName
    , emp.MiddleName
    , emp.LastName
    , ea.thirdPid
ORDER BY 
    em.CustomerName;

I get an error:

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

I've tried running the query with an explicit JOIN such as:

SELECT 
    thirdpid
    , ea.Empid
    , ea.Empname
    , em.RsCode
    , em.CustomerName AS 'RS Name'
    , SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
    , SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
    , SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
    , SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
    , SUM(CASE WHEN ea.Reason IN 
        ('National Holiday','State Holiday','Weekly Holiday','Special Holiday') 
        THEN 1 
        ELSE 0 
        END) AS 'Official Holidays'
FROM 
    EmployeeAttendance_Backup ea
    LEFT OUTER JOIN Customer em ON em.RSCode = CAST(ea.DistID AS INT)
    LEFT OUTER JOIN Employee emp ON emp.EmployeeMasterID=ea.Empid
WHERE 
    ea.markedFor >= '2012-08-26' 
    AND ea.markedFor <= '2012-09-25' 
    AND em.RSCode = ea.DistID 
    AND emp.EmployeeMasterID = ea.Empid
GROUP BY 
    ea.Empid
    , ea.Empname
    , ea.DistID
    , em.RsCode
    , em.CustomerName
    , emp.FirstName
    , emp.MiddleName
    , emp.LastName
    , ea.thirdPid
ORDER BY 
    em.CustomerName;

However, I get the same error as above.

If I remove all the SUM statements from above, I get zero records. However, if I enable even one, I get the same error.

The strange part about this error is, there is no such Employee with the name EmployeeName in any of the tables used in this query.

I am stumped as to what is happening. Can anyone shed some light?

Answer 1

I was able to solve the problem by thinking about the error message for a minute. The error message says something about not being able to convert from varchar to int. So I looked for columns that should store a number, but were actually saving it as a varchar. The necessity of a cast was an indicator.

So, I looked through each column in the EmployeeAttendance table, and found the offending record.

Empname A B was stored in a column which should have either 1 or 0 values. This column is not a BIT, but a varchar(50)

dies in shame

This table was a hand-me-down, and I think I need to get my janitors uniform out.