Select query when the value is NULL or nothing

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 2008 Forums

Transact-SQL (2008)

Select query when the value is NULL or nothing

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-23 : 16:17:56

I am having a problem with select in query: where attacheddocid is null, how can i make filename='No file' if the attacheddocid=NULL

SELECT RecipientType, Email,(select [FileName] from TAB_DocRepository where DocID=AttachedDocid) as DocRepFilename, attacheddocid FROM TAB_Recipients WHERE ModuleID = 22

attacheddocid field is INT datatype.
I also did this to select in query, but doesn't work: select isnull([FileName],'No File') from TAB_DocRepository where DocID=AttachedDocid

Thank you very much for the helpful info.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 16:36:15

SELECT RecipientType, Email,
coalesce((select [FileName] from TAB_DocRepository where DocID=AttachedDocid),'No File') as DocRepFilename,
attacheddocid
FROM TAB_Recipients
WHERE ModuleID = 22

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-23 : 16:40:17

Nigel, Thank you very much.

It worked.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 16:44:48

Another way is

SELECT RecipientType, Email,
coalesce([FileName],'No File') as DocRepFilename,
attacheddocid
FROM TAB_Recipients
left join TAB_DocRepository
on DocID=AttachedDocid
WHERE ModuleID = 22

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources