Can Someone Help Me Create A Derived Field In A View

I am a C# developer who was forced to use a DataSet with Infragistics grid instead of the class object I was using as a data source, so that I can implement an AddNew functionality directly in the grid.

In code I would simply add a read only property to do this, and it was much simpler, but if someone could point in the right direction as to can a view do what I want.

The field I want to create is called "Info" short for HasInfo.
Info has 3 values (from an enumeration) NoInfo = 0; HasResume = 1; HasHomeInfo = 2

The business logic for this is if we have a resume, I show a resume icon in the grid, if we do not have a resume, if we have home phone # or mobil # then an icon of phones are shown in the grid.

To find out if a contact has a resume the SQL is going to be something like if exists (select ContactResumeID from ContactResume Where ContactResume.ContactID = Contact.ContactID) then 1; and if that is not true; check if Contact.HomePhone is Not Null Or Empty or Contact.WorkPhone is not null or empty then 2 else 0

I can do this in C# blindfolded, but in SQL I am fish out of water for compound if statements.

The SQL for this view is shown below (creating using SQL Server Gui Selector which I think is real cool).


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[Employees]

AS

SELECT dbo.Company.CompanyID,

dbo.Company.Name AS CompanyName,

dbo.Contact.FirstName,

dbo.Contact.LastName,

dbo.Position.Title,

dbo.Department.DepartmentName,

dbo.ContactPosition.WorkPhone,

dbo.ContactPosition.Extension,

dbo.Contact.ContactID,

dbo.ContactPosition.ContactPositionID,

dbo.Department.DepartmentID,

dbo.Position.PositionID

FROM dbo.ContactPosition INNER JOIN dbo.Contact

ON dbo.ContactPosition.ContactID = dbo.Contact.ContactID INNER JOIN dbo.Position

ON dbo.ContactPosition.PositionID = dbo.Position.PositionID INNER JOIN dbo.Department

ON dbo.Position.DepartmentID = dbo.Department.DepartmentID INNER JOIN dbo.Company

ON dbo.Position.CompanyID = dbo.Company.CompanyID

Thanks,

Flying Elephant Software