Solved

How to replace non D values in column with blank

Posted on 2013-05-15
6
269 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
6 Comments
 
LVL 16

Accepted Solution

by:
santoshmotwani earned 333 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 24

Assisted Solution

by:chaau
chaau earned 167 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 333 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 62
Delete from table 6 47
How can I get this column in my query? 2 48
Getting max record but maybe not use Group BY 2 29
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …

832 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