2

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?

|improve this question

closed as too localized by Aaron Bertrand♦, Martin Smith, JNK Sep 26 '12 at 12:46

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    For starters, why are you grouping by Empname, DistId, Firstname etc. when those columns aren't in the select list? Also are you sure that Empid and EmployeeMasterID are numeric? What column is EmployeeName A B stored in? Why is there a comma after ea in the second query? Are you really showing us what you're running? And as an aside, the second form (minus the syntax error) is the form you should be using. – Aaron Bertrand♦ Sep 25 '12 at 16:55
  • I was debugging the query, and incorrectly posted an imcomplete version. Apologies. Empid and EmployeeMasterID are numeric. EmpName A B is stored in EmployeeAttendance_Backup but, there is no such record. I checked with a separate query. There was a comma, because I copied the first query and forgot to correctly edit. Yes, this is the complete, unhindered query. Only EmpName is changed. – theTuxRacer Sep 25 '12 at 17:05
  • 1
    What data type is DistID? Why do you have to cast it as an int? If it's a string, isn't it possible that this value is there? How are you convinced no such record exists? SQL Server doesn't make up fake data to inject into error messages to mess with you - it got that from somewhere in one of these tables. Note that SQL Server can evaluate your clauses in any order, so just because a bad string value is filtered out using the where clause or join clauses, doesn't mean evaluation of that value doesn't happen before it is filtered out. – Aaron Bertrand♦ Sep 25 '12 at 17:09
4

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.

|improve this answer
  • 1
    This is a good lesson on using appropriate data types, I think. – JNK Sep 26 '12 at 12:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.