Dear all, May I ask how I can extract all students' OLE records for the academic year in one excel file ? I would like to extract the following items: 註冊編號 (REG. NO.) 班別 班號 學生名稱 課外活動名稱 職位 表現 Thanks a lot {:3_60:}
For extracting data from the SLP module, pls refer to This For student activities, pls try the below SQL: select i.regno '註冊編號', i.CLASSLVL '級別', i.CLASSCODE '班別', i.CLASSNO '班號', i.CHNAME '中文姓名', i.ENNAME '英文姓名', c.CH_DES '活動名稱', d.CH_DES '職位', e.CH_DES '表現' from TB_NAA_STUDENTNAA a join TB_NAA_NAAOFFERED b on a.SUID = b.SUID and a.NAAOFFEREDID = b.NAAOFFEREDID and b.SCHYEAR = ? join TB_HSE_COMMON c on a.SUID = c.SUID and b.NAACODE = c.CODE_ID and ((c.TB_ID = 'ECACD' and b.NAATYPE = 'E') or (c.TB_ID = 'INTERSCH' and b.NAATYPE = 'I') or (c.TB_ID = 'SEVPST' and b.NAATYPE = 'S')) left outer join TB_HSE_COMMON d on a.SUID = d.SUID and d.TB_ID = 'ECAPST' and a.NAAPOSTCODE = d.CODE_ID left outer join TB_HSE_COMMON e on a.SUID = e.SUID and e.TB_ID = 'ECAPFM' and a.NAAPERFCODE = e.CODE_ID left outer join TB_NAA_ACTCAT_MAPPING f on a.SUID = f.SUID and b.NAACODE = f.NAACODE and b.NAATYPE = 'E' left outer join TB_HSE_COMMON g on a.SUID = g.SUID and g.TB_ID = 'ACTCAT' and f.ACTCATCODE = g.CODE_ID join VW_STU_LATESTSTUDENT i on a.SUID = i.SUID and a.STUID = i.STUID and b.SCHYEAR = i.SCHYEAR order by i.SCHSESS, i.CLASSLVL, i.CLASSCODE, i.CLASSNO