SELECT columnA if not null or else columnB

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)

SELECT columnA if not null or else columnB

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-12 : 13:03:48

I have a query that needs to output a column if it is populated or else another column:

SELECT CommonAppID
      ,AppID
      ,AppName
      ,FileName  -- Output this column if populated, or else
      ,ExeName   -- this column. We can call it ExeFileName
      ,ExeDesc
  FROM CommonApp

So, I thought some kind of a Case statement, but I can't get it.


SELECT CommonAppID
      ,AppID
      ,AppName
      ,Case FileName when Null then ExeName else FileName End As ExeFileName
      ,ExeDesc
  FROM CommonApp

I tried a nested SELECT in the THEN clause, but couldn't get that right either.

Duane

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 13:10:02

[code]
SELECT CommonAppID
,AppID
,AppName
,Case when FileName IS NULL then ExeName else FileName End As ExeFileName
,ExeDesc
FROM CommonApp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-03-12 : 13:23:19

[code]
SELECT CommonAppID
,AppID
,AppName
,coalesce(FileName, ExeName) As ExeFileName
,ExeDesc
FROM CommonApp

[/code]

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-12 : 13:44:37

I figured it out on my own, but learned something from these responses. They shortened my code, at least, and I learned about coalesce. However, neither of these worked fully as I thought because my data had blanks in it as well as NULLS. So, I learned about the NullIf() function. My working result I have here, along with the ExeName and FileName to test my logic:


SELECT CommonAppID
      ,AppID
      ,AppName
      ,Case when nullif(FileName,'') IS NULL then ExeName else FileName End As ExeFileName
      ,FileName
      ,ExeName
      ,ExeDesc
  FROM CommonApp

--AND

SELECT CommonAppID
      ,AppID
      ,AppName
      ,coalesce(nullif(FileName,''), ExeName) As ExeFileName
      ,FileName
      ,ExeName
      ,ExeDesc
  FROM CommonApp

Thank you both for your help.

Duane

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-03-12 : 14:21:31

nice, you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources