How to replace non D values in column with blank

I have this query for joining.
There is field ems, it has numerical values, E or D.
Now how to modify this query where in
if for any record ems is number i,e numeric value or 'E'  values it should occur as blank.
and for ems ='D' it should be shown as 'D'

select a1.*,b.photo,b.subjectcode,b.subjectname,b.ems  from (select institute,cb,semester,UID,sname,
smobile ,isnull(fmobile,'') as 'fmobile',isnull(fname,'')  as fname,isnull(mname,'') as 'mname',
centreno as 'Centreno',centre as 'Centre' from custudents  a where cb='MBA' and eflag=1
group by institute,cb,semester,UID,sname,smobile ,isnull(fmobile,''),
isnull(fname,'')  ,isnull(mname,'') ,
centreno ,centre)
as a1,mstmassstudents b
where a1.uid=b.uid and a1.cb='MBA'
order by a1.uid,b.subjectcode
searchsanjaysharmaAsked:
Who is Participating?
 
santoshmotwaniConnect With a Mentor Commented:
select a1.*,b.photo,b.subjectcode,b.subjectname,
case b.ems
when 'D' then 'D'
else ' '
  from (select institute,cb,semester,UID,sname,
smobile ,isnull(fmobile,'') as 'fmobile',isnull(fname,'')  as fname,isnull(mname,'') as 'mname',
centreno as 'Centreno',centre as 'Centre' from custudents  a where cb='MBA' and eflag=1
group by institute,cb,semester,UID,sname,smobile ,isnull(fmobile,''),
isnull(fname,'')  ,isnull(mname,'') ,
centreno ,centre)
as a1,mstmassstudents b
where a1.uid=b.uid and a1.cb='MBA'
order by a1.uid,b.subjectcode
0
 
chaauConnect With a Mentor Commented:
Replace d.ems in your query with this:

CASE WHEN d.ems = 'D' THEN 'D' ELSE '' END

Open in new window

0
 
santoshmotwaniConnect With a Mentor Commented:
select a1.*,b.photo,b.subjectcode,b.subjectname,
case b.ems
when 'D' then 'D'
else ' '
end b.ems
  from (select institute,cb,semester,UID,sname,
smobile ,isnull(fmobile,'') as 'fmobile',isnull(fname,'')  as fname,isnull(mname,'') as 'mname',
centreno as 'Centreno',centre as 'Centre' from custudents  a where cb='MBA' and eflag=1
group by institute,cb,semester,UID,sname,smobile ,isnull(fmobile,''),
isnull(fname,'')  ,isnull(mname,'') ,
centreno ,centre)
as a1,mstmassstudents b
where a1.uid=b.uid and a1.cb='MBA' 
order by a1.uid,b.subjectcode

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
PortletPaulfreelancerCommented:
there's just an END missing in your original query in that case expression as indicated here
0
 
santoshmotwaniCommented:
yep !! i fixed it and did a repost

Ta
0
 
searchsanjaysharmaAuthor Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.