how to use case statement to check if column is null

Question

I have to create a stored procedure by using 3 tables,1st is Registration table,job seeker table,employment detail table. In that I am having entries in 1st two tables.Till now I am not having any entry in employment detail table.

I am having following query:

select RD.FirstName,RD.LastName,
(select case when JR.profileHeadline=''
then 'No Resume Headline mentioned' 
else JR.profileHeadline end
from jobseekerReg JR)as profileHeadline ,
(select case when ED.designation=''
then 'No designation mentioned'
else ED.designation end
from employmentDetail ED)as designation,
(select case when ED.companyName=''
then 'No company name mentioned'
else ED.companyName end
from employmentDetail ED) as companyName,JR.location,
(select case when ED.funcArea=''
then 'No functional area mentioned'
else ED.funcArea end
from employmentDetail ED) as funcArea ,
(select case when ED.cmpIndustry=''
then 'No industry mentioned'
else ED.cmpIndustry end
from employmentDetail ED)as cmpIndustry,RD.BirthDay,
RD.Gender,JR.experience,
(select case when ED.salary=''
then 'No salary mentioned'
else ED.salary end
from employmentDetail ED)as salary ,JR.mobileNumber,JR.emailId,
JR.altEmailID,JR.jobSeekerAddrs,JR.maritalStatus,
(select case when JR.keySkills=''
then 'No keyskills mentioned'
else JR.keySkills end
from jobseekerReg JR)as keySkills
from RegistrationDetails RD join jobseekerReg JR on RD.Reg_Id=JR.regId
left outer join employmentDetail ED on ED.regId=JR.regId 
and ED.regId=2  where RD.Reg_Id=JR.regId and RD.Reg_Id=2 and JR.regId=2

The above query gives me correct output,but the problem is as I am not having any entry in employment table for regId=2, columns in this table gives output as null. how should I handle this problem? suggest me any solution Thanks in advance.

Use IsNull(fieldname, 'default value') to set default data to null fields if that is what you want. Else there could be problem with populating the field with values. — Nisha
– Nisha, Commented Oct 1, 2013 at 10:03

Alexander Simonov · Accepted Answer · 2013-10-01 08:18:49Z

1

CASE WHEN ED.Salary IS NULL THEN 'No salary mentioned' ELSE ED.Salary END

But I think that in your case ISNULL() would be better:

select RD.FirstName,RD.LastName,
ISNULL(JR.profileHeadline, 'No Resume Headline mentioned') as profileHeadline ,
ISNULL(ED.designation, 'No designation mentioned') as designation,
ISNULL(ED.companyName, 'No company name mentioned')  as companyName, 
JR.location,
ISNULL(ED.funcArea, 'No functional area mentioned') as funcArea ,
ISNULL(ED.cmpIndustry, 'No industry mentioned') as cmpIndustry,
RD.BirthDay,
RD.Gender, JR.experience,
ISNULL(ED.salary, 'No salary mentioned' as salary ,
JR.mobileNumber,JR.emailId,
JR.altEmailID,JR.jobSeekerAddrs,JR.maritalStatus,
ISNULL(JR.keySkills, 'No keyskills mentioned') as keySkills
from RegistrationDetails RD join jobseekerReg JR on RD.Reg_Id=JR.regId
left outer join employmentDetail ED on ED.regId=JR.regId 
and ED.regId=2  where RD.Reg_Id=JR.regId and RD.Reg_Id=2 and JR.regId=2

answered Oct 1, 2013 at 8:18

Alexander Simonov

1,5841 gold badge9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashwini Agivale Over a year ago

the above query works fine for fields from employment details.And it is not giving any value in fields from registration table and job seeker table

Alexander Simonov Over a year ago

@AshwiniAgivalem, you should look for a problem in your data. Have I answered on your original question?

Collectives™ on Stack Overflow

how to use case statement to check if column is null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related