Post reply

In a t-sql 2012, I can trying to make a case statement work in an update statem

  • July 15, 2019 at 3:07 pm

    #3663056

    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'?
    LEFT JOIN(select SCHOOLNUM,STULINK,SCHOOLYEAR,STATUS,TOT_ABSENCES = sum(EXCCNT) + sum(UNECNT)from Details AtrnLtrDetailswhere schoolyear = (select max(schoolyear) FROM Semester)Group By SCHOOLNUM,STULINK,SCHOOLYEAR,STATUS)detailson details.SCHOOLNUM = Milestone.SCHOOLNUMand details.STULINK = Milestone.STULINKand details.SCHOOLYEAR = Milestone.SCHOOLYEARwhere 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?

    t-sql 2012 case statement within an update statement

  • July 15, 2019 at 3:24 pm

    #3663060

    I suspect that it's something to do with having the same name for your subquery (details) as for the underlying table (Details).  Try a case-sensitive search and replace and change "details" to "d".

    John

  • July 15, 2019 at 5:14 pm

    #3663117

    I think the Milestone table does not contain a column named "TOT_ABSENCES".  Please review the column names in the Milestone table.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • July 16, 2019 at 6:33 pm

    #3663531

    Thanks a lot!

Viewing 4 posts - 1 through 3 (of 3 total)

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