query????????? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query?????????

Author

Topic

sweet_777
Starting Member

16 Posts

Posted - 2010-10-06 : 23:42:50

Hi all,

i my table dept, 2 columns are there. 1.email 2.fax. 3.module

email, fax , module
contactemail , contactfax , DE
studymanageemail , managefax , DE
addemail , addfax , DE
siteemail , null , DE
monitoremail , null , DE

i have written query like this :

SELECT email,fax from dept where module='DE'
I AM getting all values but i don't want null values.
email , fax contactemail , contactfax studymanageemail , managefax addemail , addfax siteemail , null monitoremail , null

i want o/p like this:

email , fax contactemail , contactfax studymanageemail , managefax addemail , addfax siteemail , monitoremail Regards
Rama

Thanks & Regards
Sweet_77

razeena
Yak Posting Veteran

54 Posts

Posted - 2010-10-07 : 00:38:43

You can replace null with some other value using isnull checking...

declare @test table(email varchar(20),fax varchar(10),module varchar(10))
insert into @test
select 'contactemail' , 'contactfax' , 'DE'
union all select 'studymanageemail' , 'managefax' , 'DE'
union all select 'addemail' , 'addfax' , 'DE'
union all select 'siteemail' , null , 'DE'
union all select 'monitoremail' , null , 'DE'
select email,fax from @test where module='DE'

select email,isnull(fax,0)as fax from @test where module='DE'

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-10-07 : 00:45:11

I don't know what you are expecting.

But try this,

Case 1:
SELECT coalesce(email,'') as email,coalesce(fax,'') as fax from dept where module='DE'

Case 2:
SELECT email from dept where module='DE' and not(email is null)
union
SELECT fax from dept where module='DE' and not(fax is null)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources