?
Solved

SQL IF Statement

Posted on 2012-08-16
6
Medium Priority
?
541 Views
Last Modified: 2012-09-07
Dear Experts,

I am looking for the method to complete the following.

Choose an ID from a column where if column B is Null use A-ID else use B-ID and return the firstName

 select	top	10 
			Person.firstName 
from		localhost.[conv].dbo.__LegacyActivity LegAct

		left outer join	localhost.[conv].dbo.__LegacyActivity_CustomAttributesManager LegAct_CAM
		on				LegAct.customAttributes = LegAct_CAM.oid
		left outer join	localhost.[conv].dbo._Person as Person
		on				LegAct_CAM.assignedTo = Person.oid;

Open in new window


Thanks
0
Comment
Question by:BIT_BOPPER
6 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 38302359
case
  when columnB is null then A-ID
  else coalesce(B-ID, '') + coalesce(' - ' + firstname, '')
end as IDcolumn
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38302529
Needs a couple of slight adjustments:

case
  when columnB is null then cast(A-ID AS varchar(10))
    else cast(coalesce(B-ID, '') as varchar(10)) + coalesce(' - ' + firstname, '')
end as IDcolumn
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 38303471
you have to use isnull(B.ID,A.ID) function for replacing null value with a.ID value.

i am not getting your exact sql query where you have used ID.


Thanks,
Nishant
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 31

Accepted Solution

by:
MlandaT earned 2000 total points
ID: 38304173
Make use of the ISNULL function
select	top	10 
                        isnull(B.ID,A.ID) AS ID,
			Person.firstName 
from		localhost.[conv].dbo.__LegacyActivity LegAct

		left outer join	localhost.[conv].dbo.__LegacyActivity_CustomAttributesManager LegAct_CAM
		on				LegAct.customAttributes = LegAct_CAM.oid
		left outer join	localhost.[conv].dbo._Person as Person
		on				LegAct_CAM.assignedTo = Person.oid;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 38306213
Perhaps some sample data and the desired results would help us to understand your problem.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 38307323
i will try for it u need to check it

SELECT      TOP      10
            ISNULL(Person.oid , LegAct_CAM.oid ) AS oid , Person.firstName
FROM            localhost.[conv].dbo.__LegacyActivity LegAct
            LEFT OUTER JOIN      localhost.[conv].dbo.__LegacyActivity_CustomAttributesManager LegAct_CAM
            ON                        LegAct.customAttributes = LegAct_CAM.oid
            LEFT OUTER JOIN      localhost.[conv].dbo._Person AS Person
            ON                        LegAct_CAM.assignedTo = Person.oid;
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question