Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The Query Designer does not support the CASE SQL construct. Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2008 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2008 4:00 AM
Points: 27, Visits: 38
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
Post #460195
Posted Tuesday, February 26, 2008 8:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 30, 2012 9:00 PM
Points: 7, Visits: 93
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).

More info
Post #460667
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse