How to!
-
I keep getting an error of "Invalid column name 'ClosedFiveDaysTot'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ClosedTot'".
My query is below. Both of the columns ClosedFiveDaysTot & ClosedTot are not real columns in the view, it is a calculated field from the case statement. Can someone tell me where I'm going wrong here?
SELECT UserID, ComplaintType, COUNT(CategoryDescript) AS ClosedTot,
SUM(CASE WHEN (ClosedDateTime - OpenedDateTime) <= 7 THEN 1
ELSE 0 END) AS ClosedFiveDaysTot, (ClosedFiveDaysTot / ClosedTot) AS 'BRIPct'
FROM vwComplaintDetails
WHERE ComplaintType = 'C'
GROUP BY UserID, ComplaintType -
If I see this right, you can't use this alias, but rather need to repeat your formulas.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hm, just out of curiosity, where do you see here derived column names in the WHERE clause?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Going back to the derived column names (column aliases)...replace ClosedTot with the actual formula as below. I have had QA bark at me when I've used the alias rather than the actual formula.
SELECT UserID
, ComplaintType
, COUNT ( CategoryDescript ) AS ClosedTot
, SUM ( CASE WHEN (ClosedDateTime - OpenedDateTime) <= 7 THEN 1
ELSE 0
END) AS ClosedFiveDaysTot
, (ClosedFiveDaysTot / COUNT(CategoryDescript)) AS 'BRIPct'
FROM vwComplaintDetails
WHERE ComplaintType = 'C'
GROUP BY UserID, ComplaintType
Hope this works for ya'.
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply