Print No Data when Value is null

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

Print No Data when Value is null

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-10 : 07:00:55

select COALESCE(NULLIF(isid,''),'No Data') as Spon from members
where isid='U100000002' and aid not like 'R%'
group by isid

I need no Data But it gives Blank. Please help me

Ved Prakash Jha

aprichard
Yak Posting Veteran

62 Posts

Posted - 2008-09-10 : 07:11:08

Hi,

select COALESCE(isid,'No Data') as Spon from members
where isid='U100000002' and aid not like 'R%'
group by isid

if value of isid is null, that will return 'No Data'

By
Richard

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-10 : 07:22:27

I m talking about When fetched Data is null
Then query will shows me 'No Data'

select COALESCE(isid,'No Data') as Spon from members
where isid='U100000002' and aid not like 'R%'
group by isid

still not working

Ved Prakash Jha

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-10 : 07:26:13

Declare @tbl1 Table
( ID int,nam varchar(20))

Insert into @tbl1
Select 1,'sunil' union all
select 2,NULL

Select COALESCE(nam,'No Data') as Col from @tbl1

Whats the data type of isid? COALESCE will convert result to higher precedence datatype. Check
COALESCE in BOL.

aprichard
Yak Posting Veteran

62 Posts

Posted - 2008-09-10 : 07:26:28

can u explian briefly

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-10 : 07:30:35

isid varchar(10)

plz explain in brief

Ved Prakash Jha

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-10 : 07:37:15

quote:
Originally posted by vedjha

isid varchar(10)

plz explain in brief

Ved Prakash Jha

Not sure why you are not getting 'No Data' if isId is NULL. I tried below code and it shows 'No Data'

Declare @tbl2 Table
( ID varchar(10),nam varchar(20))

Insert into @tbl2
Select 'U123456789','sunil' union all
select NULL,'another' union all
select NULL ,'kumar'
Select COALESCE(ID,'No Data') as Col from @tbl2

Can you provide sample data, table structures and expected output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources