[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to replace non D values in column with blank

Posted on 2013-05-15
6
Medium Priority
?
281 Views
Last Modified: 2013-05-16
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
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Accepted Solution

by:
santoshmotwani earned 999 total points
ID: 39170432
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
 
LVL 25

Assisted Solution

by:chaau
chaau earned 501 total points
ID: 39170433
Replace d.ems in your query with this:

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

Open in new window

0
 
LVL 16

Assisted Solution

by:santoshmotwani
santoshmotwani earned 999 total points
ID: 39170438
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39170467
there's just an END missing in your original query in that case expression as indicated here
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39170480
yep !! i fixed it and did a repost

Ta
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 39170782
tx
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question