The Query Designer does not support the CASE SQL construct.

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply