If then and add a field in SQL 1

[wsproperties]

Using sql 2005, I have 2 fields, bioID and MillID. I want to create and populate another field, lets call it Combine. If the contents of bioID equal MillID then I want to print 'N', else if the contents of bioID are not equal to MillID I want to populate the same field with a 'Y' So far my structure looks like this

select bioID, MillId, LName, FName
From biotable left outer join Name
on nameid = bioID

How would I accomplish what i said up top and where would I place the if then code.

 

[SQLSister]

YOu don't use if, you use case. Look up the syntax in books online.

Do you want to create an actual column or just one for the purposes of the select?


"NOTHING is more important in a database than integrity." ESquared

 

[wsproperties]

It would be a column called Combine

 

[bborissov]

You have 3 choices:
1. to do this in your query:

select bioID,
       MillId,
       LName,
       FName,
       CASE WHEN bioID = MillId
                 THEN 'N'
       ELSE 'Y' END AS Combine
From biotable
left outer join Name
on nameid = bioID

2. Create calculated field table (check in BOL how to do this)


3. If this is ONLY for representation (print reports or some other job in your FrontEnd) THEN do this in your front End, not in SQL Server. I didn't see any difficulties to print a different value in some report based on some logic.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[BrianB]

Are you sure you want to store something dependent on the value of two fields? If you can always calculate the answer, you could run into problems on updates. If somebody updates bioID but doesn't run the check, your combine column will be wrong.

If you must store this value, I would suggest populating it with a trigger. That way you can ensure the value in Combine gets changed when bioID and milID do.

Brian Begy
Chief Database Guy
Chicago Data Solutions

http://www.chicagodatasolutions.com