SQL LEAVE SCHOOL

本文由 0004 在 2022-05-30 發表於 "WebSAMS 討論區" 討論區

  1. 55094568

    0004
    Expand Collapse

    文章:
    41
    讚:
    0
    抽時候 出現左
    SQL 錯誤: Exception:SQL Anywhere Error -143: Column 'STRN' not found

    謝謝



    select

    a.schyear||'/'||a.schyear+1 'School Year',
    a.classlvl 'Class Level',
    a.SCHYEAR '學年',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    b.CHNAME '姓名',
    b.enname 'Eng Name',
    b.SEX '性別',

    a.STRN 'STRN',

    dateformat(c.LASTATTEND, 'DD/MM/YYYY') '最後出席日期',

    h.CH_DES '原因'


    from TB_STU_STUSCHREC a
    left outer join TB_STU_STUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID
    left outer join TB_STU_DEPARTURE c
    on a.SUID = c.SUID and a.STUID = c.STUID and
    a.STUSCHRECID=c.STUSCHRECID
    left outer join TB_HSE_COMMON h
    on a.SUID = h.SUID and h.TB_ID = 'LEVRES' and
    c.DEPREASON = h.CODE_ID
    where a.STATUS = 5 and a.SCHYEAR = ?
    order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    你好,

    TB_STU_STUSCHREC 這個TABLE並沒有STRN這個欄位名稱。
    而STRN應該儲存在TB_STU_STUDENT這個TABLE內,所以
    你提供的SQL應該改為......

    select

    a.schyear||'/'||a.schyear+1 'School Year',
    a.classlvl 'Class Level',
    a.SCHYEAR '學年',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    b.CHNAME '姓名',
    b.enname 'Eng Name',
    b.SEX '性別',

    b.STRN 'STRN',

    dateformat(c.LASTATTEND, 'DD/MM/YYYY') '最後出席日期',

    h.CH_DES '原因'


    from TB_STU_STUSCHREC a
    left outer join TB_STU_STUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID
    left outer join TB_STU_DEPARTURE c
    on a.SUID = c.SUID and a.STUID = c.STUID and
    a.STUSCHRECID=c.STUSCHRECID
    left outer join TB_HSE_COMMON h
    on a.SUID = h.SUID and h.TB_ID = 'LEVRES' and
    c.DEPREASON = h.CODE_ID
    where a.STATUS = 5 and a.SCHYEAR = ?
    order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2022-05-31