sum 연산자에 대한 피연산자 데이터 형식 varchar이(가) 잘못되었습니다.

VARCHAR 형식은 SUM을 할 수 가 없으므로

sum( convert(integer, varchar형식컬럼)  )  문자형을 숫자 형으로 변환 시키시면 됩니다. 

---

운영서버에 반영했더니 오류가 나는데..

 

http://nejooso.com/trackback/1491

 

운영서버에는 이런 형식으로  바꿔줘야 하는건.. 뭔지.. 

 

SELECT 
 M.EMP_NO
,MAX(M.EMP_KO_NM) AS EMP_KO_NM
,MAX(B.CMPNY_NM) AS CMPNY_NM 
,MAX(A.DEPT_NAME) AS DEPT_NAME 
,MAX(C.MID_NAME) AS JOB_CLASS_NM 
,SUM(cast(S.LATER_GB as decimal(3) )) AS LATER_COUNT /* 지각 */
,SUM(cast(S.EARLY_GB as decimal(3) )) AS EARLY_COUNT /* 조퇴 */
,ISNULL ( CASE MAX(S.ABS_GB) WHEN '1' THEN SUM(cast(S.ABS_GB as decimal(3) )) END ,0 ) ABS_COUNT /* 결근 */
,ISNULL ( CASE MAX(S.NGH_TM_GB) WHEN '1' THEN SUM(cast(S.NGH_TM_GB as decimal(3) )) END ,0 ) NGH_TM_COUNT1 /* 야근1 */
,ISNULL ( CASE MAX(S.NGH_TM_GB) WHEN '2' THEN SUM(cast(S.NGH_TM_GB as decimal(3) )) END ,0 ) NGH_TM_COUNT2 /* 야근2 */ 
,ISNULL ( CASE MAX(S.HOL_TM_GB) WHEN '1' THEN SUM(cast(S.HOL_TM_GB as decimal(3) )) END ,0 ) HOL_TM_COUNT1 /* 휴일1 */
,ISNULL ( CASE MAX(S.HOL_TM_GB) WHEN '2' THEN SUM(cast(S.HOL_TM_GB as decimal(3) )) END ,0 ) HOL_TM_COUNT2 /* 휴일2 */
FROM OBH_EMPBAS M LEFT OUTER JOIN OBA_DEPT_INFO A ON M.DEPT_CD = A.DEPT_CODE
                  LEFT OUTER JOIN OBA_CMPNY_INFO B ON A.CMPNY = B.CMPNY
                  LEFT OUTER JOIN OBA_MID_CODE C ON M.BLONG_LARGE = C.LARGE_CODE AND M.BLONG_TO = C.MID_CODE
    ,OBH_DAILY_GT S
WHERE M.EMP_NO = S.EMP_NO
AND   M.ENGAG_DIV != '102'    
GROUP BY M.EMP_NO

2013. 5. 2. 10:23