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:
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
FROM Milestone Milestone
TOT_ABSENCES = sum(EXCCNT) + sum(UNECNT)
from Details AtrnLtrDetails
where schoolyear = (select max(schoolyear) FROM Semester)
Group By SCHOOLNUM,
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?