CODEWITHSUNDEEP
varchar
user1958651
user1958651

Reputation: 451

CONVERTING VARCHAR to DATA TYPE INT

I have the following code. The Case statements are converting the listed numbers to text and the rest of the codes into NULL, but i keep getting the following error: CONVERSION FAILED WHEN CONVERTING THE VARCHAR VALUE 'RDG5' TO DATA TYPE INT. RDG5 is one of the many codes being converted to NULL. Any thoughts?

    SELECT     dbo.TACCPLI.SYS_EMP_ID_NR, MAX(dbo.TACCPLI.AML_TYP_CD) AS DEG, (CASE WHEN dbo.TACCPLI.AML_TYP_CD IN (20, 25) 
                      THEN 'Associates' WHEN dbo.TACCPLI.AML_TYP_CD IN (30, 35) THEN 'Bachelors' WHEN dbo.TACCPLI.AML_TYP_CD IN (40, 45) 
                      THEN 'Masters' WHEN dbo.TACCPLI.AML_TYP_CD IN (50) THEN 'PhD' ELSE NULL END) AS Degree_Level, dbo.TACCPLI.AML_TYP_CD
FROM         dbo.TACCPLI RIGHT OUTER JOIN
                          (SELECT     dbo.v_TMS_employee_HR.GEMSID, dbo.v_TMS_employee_HR.TMS_ID
                            FROM          dbo.v_TMS_employee_HR INNER JOIN
                                                   dbo.v_sc17_TMS_Data_Career_Meeting_Rating ON 
                                                   dbo.v_TMS_employee_HR.TMS_ID = dbo.v_sc17_TMS_Data_Career_Meeting_Rating.Employee_ID
                            WHERE      (LEFT(dbo.v_sc17_TMS_Data_Career_Meeting_Rating.Meeting_Readiness_Rating, 2) IN ('14', '15')) AND 
                                                   (dbo.v_TMS_employee_HR.Job_Group_Code >= '72') AND (dbo.v_TMS_employee_HR.Job_Group_Code <= '79') AND 
                                                   (dbo.v_sc17_TMS_Data_Career_Meeting_Rating.Plan_Year = 2012) AND (dbo.v_TMS_employee_HR.Region NOT IN ('12', '13', '16', '17'))
                            GROUP BY dbo.v_sc17_TMS_Data_Career_Meeting_Rating.Meeting_Readiness_Rating, dbo.v_TMS_employee_HR.GEMSID, dbo.v_TMS_employee_HR.TMS_ID) 
                      AS HPS ON dbo.TACCPLI.SYS_EMP_ID_NR = HPS.TMS_ID
GROUP BY dbo.TACCPLI.SYS_EMP_ID_NR, dbo.TACCPLI.AML_TYP_CD

Upvotes: 3

Views: 467

Answers (1)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Based on your comment

Hmm it's trying to convert that so it can execute the case statement then. Not sure I like that, to me it should have just failed to parse it because of a type mismatch.

Ho Hum

This is a fragile nasty bodge but if AML_TYP_CD can be relied on to always have it's last two chars being numeric then 

CASE WHEN Right(dbo.TACCPLI.AML_TYP_CD,2) IN ('20', '25')

might be a way to go, but seeing as '20' and '25' etc have their own meaning, they should have their own column.

Upvotes: 1

Related Questions