question

palum avatar image
palum asked

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

I have the below two tables and i want to show the emp status of the students.In the t1.empstatus coloumn there are some characters like N,K,''. I want to show those students as employmentstatus unknown. but when i am trying to do its showing conversion failed below is my query select t1.name,t1.empstatus,t2.empstatus_id,t2.name from student t1 left join employment t2 on t1.empstatus = t2.empstatus_id where t1.empstatus = case when isnumeric(t1.empstatus)) = 0 then 5 end STUDENT t1 rno name class empstatus(varchar) 1 a c 1 2 b c 2 3 c c 4 4 d a N EMPLOYMENT t2 code name empstatus_id(int) 1 fulltime 1 2 parttime 2 3 unemployed 4 4 unknown 5
tsqlcase
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
is this a homework question? Still happy to help, but might answer it somewhat differently.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
The convert error comes from the join condition `t1.empstatus = t2.empstatus_id` so that is where you should put the logic to replace the char data with an int, such as from @student t1 left join @employment t2 on case when isnumeric(t1.empstatus) = 0 then 5 else t1.empstatus end = t2.empstatus_id but that doesn't seem like a good way to proceed ... `ISNUMERIC()` will return 1 for any value that can be converted to one of the numeric datatypes including money, float, etc which means that unexpected results can appear for strings including non-numbers , see for more discussion. Maybe you could use an alternative to ISNUMERIC() like discussed here?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.