SQLTeam.com | Weblogs | Forums

T-sql 2012 case statement within an update statement

In a t-sql 2012, I can trying to make a case statement work in an update statement and I am getting the error ' Msg 207, Level 16, State 1, Line 5 Invalid column name 'TOT_ABSENCES'.
from the following sql:

UPDATE Milestone
set TOT_ABSENCES = case when (details.TOT_ABSENCES is not null) or isnumeric(details.TOT_ABSENCES) = 0 or Milestone.ABSENCES < 5 or details.TOT_ABSENCES < 5 then 5 else
case when details.TOT_ABSENCES < ABSENCES then ABSENCES else details.TOT_ABSENCES
end
end

FROM Milestone Milestone
LEFT JOIN
(
select SCHOOLNUM,
STULINK,
SCHOOLYEAR,
STATUS,
TOT_ABSENCES = sum(EXCCNT) + sum(UNECNT)
from Details AtrnLtrDetails
where schoolyear = (select max(schoolyear) FROM Semester)
Group By SCHOOLNUM,
STULINK,
SCHOOLYEAR,
STATUS
)details
on details.SCHOOLNUM = Milestone.SCHOOLNUM
and details.STULINK = Milestone.STULINK
and details.SCHOOLYEAR = Milestone.SCHOOLYEAR
where milestone.Milestone_CODE= '005'

I want to make certain the details.TOT_ABSENCES is not null, is numeric and the values need to be at least 5. Thus can you show me what I can do to solve the problem?

Does the milestone table have a tot_absences column? Post your table schema.