Create a view with an If Function (SQL Enterprise Manager)

[kemorr]

Hi everyone,

I am trying to creat a view of a table but include an if function on column 'WARD' the primary table is called tbl_Discharge and the table with exclusions is called tbl_Profile_Detail

If the data from tbl_Discharge.Ward is in tbl_Profile_Detail.Data then return "OTHER" else return tbl_Discharge.Ward

Heres what I have got but I don't know how or if you can right If functions into an sql view

SELECT dbo.tbl_Discharge.CRN, dbo.tbl_Discharge.Discharge_Date, { fn HOUR(dbo.tbl_Discharge.Discharge_Time) } AS Hour, dbo.tbl_Discharge.Consultant,
dbo.tbl_Discharge.DOW
FROM dbo.tbl_Discharge LEFT OUTER JOIN
dbo.tbl_Profile_Detail ON dbo.tbl_Discharge.Ward = dbo.tbl_Profile_Detail.Data

[HansV]

You could try a CASE expression:

SELECT dbo.tbl_Discharge.CRN, dbo.tbl_Discharge.Discharge_Date, { fn HOUR(dbo.tbl_Discharge.Discharge_Time) } AS Hour,
dbo.tbl_Discharge.Consultant, dbo.tbl_Discharge.DOW, TheWard = CASE dbo.tbl_Profile_Detail.Data
WHEN dbo.tbl_Profile_Detail.Data Is Null THEN dbo.tbl_Discharge.Ward
ELSE 'Other'
END
FROM dbo.tbl_Discharge LEFT OUTER JOIN
dbo.tbl_Profile_Detail ON dbo.tbl_Discharge.Ward = dbo.tbl_Profile_Detail.Data