Can I "Select" one of two columns based on the value in the first? 3

[nakkii]

Can I perform a select statement that will return the value in one of two columns based on the value in one of the columns?

Here's my situation: I have an Employee table with the columns FirstName, LastName and OtherName. The OtherName column is for nicknames or what the person goes by - example: FirstName = Robert, OtherName = Bob

What I want to do is provide a view for an application and the view should show the person's preferrd name: "Bob Jones" instead of proper name: "Robert Jones". I am performing a query and concatonating the two fields, if the OtherName column is 'NULL' then I want the query to pull the FirstName.

I know this is easy to do through the application the problem is it is actually several applications that currently point to an Access DB but the company has grown and so we have a new HR database on SQL and this has become an issue and so I want to create a view that mimics the Access table.

Thanks for any help you can offer......

 

[tlbroadbent]

Use the CASE function as in the following example.

Select
Case When OtherName Is Null
Then FirstName
Else OtherName End + ' ' + LastName
From yourtable Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sunila7]

Hi,

Try this query.........


Select Case
When OtherName is Null
Then FirstName + ' ' + LastName
Else OtherName + ' ' + LastName End Name
From TBL


Sunil

 

[nakkii]

Thanks, that was it.

Although SQL's Query Designer griped at me stating it didn't support the CASE SQL contruct, it took it anyway and appears to be working.

Again many Thanks!

 

[tlbroadbent]

Most of the time, I use Query Analyzer to create queries, procedures and views due to the limitations of Enterprise Manager. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[nakkii]

Hey tlbroadbent your Computing Link is broke in your signature.

 

[tlbroadbent]

Thanks for noticing that. I've changed the link in my sig. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[rt63]

Another way without using CASE is


Select IsNull(OtherName,FirstName) + ' ' + LastName
From yourtable


RT

 

[tlbroadbent]

RT, Excellent answer! There are usually multiple ways to solve a problem. Here is one more solution using Coalesce.

Select
Coalesce(OtherName, FirstName) + ' ' + LastName
From yourtable

The advantage of Coalesce is not readily apparent in this simple query. Its advantage is in the ability to pass several parameters in the list and have the first non-null parameter chosen. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[nakkii]

Thanks, tlbroadbent. After performing the task of "OtherName" the powers to be now want multiple Other Name fields for things like Maiden Name, Nickname, and Short Names. So the Coalesce command will definately come in handy.

By the way nice web site, Lots of info!!!