February 26, 2008 at 6:34 am
when i copy this into a view it brings the following msg.
The Query Designer does not support the CASE SQL construct.
SELECT case ST.CurrentSickTerm
when 'LT' then 'Long Term'
when 'ST' then 'Short Term'
when '' then 'NULL'
END as SickTerm,
ST.CurrentSickTerm, SMS.Surname, SMS.Forenames, SMS.Title, SMS.[Job Title], SMS.SMSWard, SE.FirstDate, SE.LastDate,
SE.BaseWard, SE.PersonnelNumber, SE.BaseDirectorate, SE.SickCategory, SE.SickType, SE.SickReason, SE.IR1Number, SE.RIDDORDate,
SE.SickEpisodeID, SE.RTWInterview, SE.Deleted, HS.HospitalSiteDesc
FROM dbo.tblSicknessEpisode SE INNER JOIN
dbo.tblSMSStaff SMS ON SE.PersonnelNumber = SMS.[Personal Number] INNER JOIN
dbo.viewCurrentStaffSickTerms ST ON SE.PersonnelNumber = ST.[Personal Number] INNER JOIN
dbo.tblHospitalSite HS ON SE.BaseDirectorate = HS.HospitalSite
WHERE (SE.LastDate IS NULL) OR
(SE.LastDate >= '12/1/2006') AND (ST.CurrentSickTerm <> '') AND (SE.Deleted = 0)
ORDER BY SMS.Surname, SMS.Forenames, SE.FirstDate
February 26, 2008 at 8:36 pm
The 'funny' thing is that although Query Designer doesn't parse it, it is allowed in a View.
So, what I tend to do as a work around, is one of these 2 things:
1. Use a script to create your view, as shown in books online:
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GO
2. Create and test the view in Query Analyser, then create a View in Enterprise Manager by adding in a simple table and selecting a field. Save it, open it up and paste the query into it (from SELECT down of course).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply