Solved

How to replace non D values in column with blank

Posted on 2013-05-15
6
270 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 76
Grid querry results 41 80
user defined date datatype in SQL Server- can it be overdone.. 6 35
Please help for the below sql query. 1 28
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 …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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