Problem with Query: CASE SQL construct error.

[CoolFactor]

I am trying to create the a View with the following SQL code but I get an error message that says "The Query Designer does not support the CASE SQL construct." It also states that I have an incorrect syntax error on '2008'.

CREATE VIEW dbo.tblFinancialsSummedbyFY_FY08
AS
SELECT strWBSSubmitKey,
SUM(CASE strFY WHEN '2008' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2008,
SUM(CASE strFY WHEN '2009' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2009,
SUM(CASE strFY WHEN '2010' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2010,
SUM(CASE strFY WHEN '2011' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2011,
SUM(CASE strFY WHEN '2012' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2012,
SUM(CASE strFY WHEN '2013' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2013
FROM dbo.FY08RMP_tblFinancial
GROUP BY strWBSSubmitKey

 

[gmmastros]

Stop using Enterprise Manager to create queries and/or views.

Click Tools -> Query Analyzer.
Copy/Paste your code there.

Also, You should probably rename your columns.

CREATE VIEW dbo.tblFinancialsSummedbyFY_FY08
AS
SELECT strWBSSubmitKey,
SUM(CASE strFY WHEN '2008' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS [!][[/!]2008[!]][/!],
SUM(CASE strFY WHEN '2009' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2009,
SUM(CASE strFY WHEN '2010' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2010,
SUM(CASE strFY WHEN '2011' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2011,
SUM(CASE strFY WHEN '2012' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2012,
SUM(CASE strFY WHEN '2013' THEN FIHL + FSERV + FMATL + FTRAV + FMINO ELSE 0 END) AS 2013
FROM dbo.FY08RMP_tblFinancial
GROUP BY strWBSSubmitKey

Put the square brackets around all your column aliases. I would suggest renaming them, though. Something like "FinancialData2008". That way, you won't have alias problems.



-George

"the screen with the little boxes in the window." - Moron