SQL LEAVE SCHOOL

抽時候 出現左
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

 

你好,

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