How do I make this SQL statement return empty strings instead of NULLS?

Question

LastAccessed=(select max(modifydate) from scormtrackings WHERE 
bundleid=@bundleid and userid=u.userid),
CompletedLessons=(select Value from scormtrackings WHERE 
bundleid=@bundleid and userid=u.userid AND param='vegas2.progress'),
TotalLessons=100,
TotalNumAvail=100,
TotalNumCorrect=(SELECT Value FROM scormtrackings WHERE 
bundleid=@bundleid AND userid=u.userid AND param='cmi.score.raw')

This is only part of a large select statement used by my ASP.NET Repeater that keeps crashing when the values are NULL, I have tried ISNULL() but either it didn't work, or I did it wrong.

ISNULL((SELECT max(modifydate) FROM scormtrackings WHERE 
bundleid=@bundleid AND userid=u.userid),'') AS LastAccessed,

(...)

???

UPDATE: I've tried all these things with returning '', 0, 1, instead of the value that would be null and it still doesn't work, I wonder if the problem is with the Repeater?

Related Question:

Why does my repeater keep crashing on Eval(NULL) values?

Solution 1

You can use the COALESCE function to avoid getting nulls. Basically it returns the first non-null value from the list.

SELECT COALESCE(dateField, '') FROM Some_Table

This is an ANSI standard, though I have noticed that it isn't available in Access SQL.

Solution 2

You can use CASE:

CASE WHEN MyField IS Null THEN ''
ELSE MyField
End As MyField

Solution 3

select max(isnull(modifydate, "default date") from scormtrackings where...

will work as long as there is at least one row that satisfies the where clause, otherwise you will still get NULL

select IsNull( max(modifydate), "default_date") from scormtrackings where ...

should work in all cases

Comments

[derobert]

COALESCE is actually ANSI SQL. It's not a vendor extension.

[jrcs3]

@derobert Thanks, I updated the answer to reflect your comment.