Return null if empty - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Return null if empty

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-16 : 13:24:24

Hi,
I have a simple query that return a name and average.
My problem i want if te.strNote is NULL then show NULL

Here my query :

SELECT     (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
		   (SELECT stdfirstn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strFirstName,
		   (cast(replace(te.strNote,',','.') as float) / tee.intPonderation * 100)
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 44 
ORDER BY   tee.intEvaluationElementId,strLastName

If it null it will retun 0 because of this line
(cast(replace(te.strNote,',','.') as float) / tee.intPonderation * 100)

But i don't know how to fix it!

It will return something like this:

Name strNote
Luc 95
Paul 85
John 0
Kev 0

But i want something like this

Name strNote
Luc 95
Paul 85
John NULL
Kev 0

For Kev it ok to show 0 because he did zero on is test but John did get a Score so i want him to show NULL or nothing.

Tks in advance!
Luc

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-16 : 14:00:55

I would put a CASE WHEN statement in there so that it does what you want it to do.

CASE te.strNote
WHEN '' THEN NULL -- OR you can do LEN() thingy too
ELSE (cast(replace(te.strNote,',','.') as float) / tee.intPonderation * 100)
END

2 cents?

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-16 : 14:04:19

Hi tks it work like a charm!!

quote:
Originally posted by yosiasz

I would put a CASE WHEN statement in there so that it does what you want it to do.

CASE te.strNote
WHEN '' THEN NULL -- OR you can do LEN() thingy too
ELSE (cast(replace(te.strNote,',','.') as float) / tee.intPonderation * 100)
END

2 cents?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 23:22:49

u can use nullif also
select nullif(strnote,'') from talbename

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources