SELECT DISTINCT C.IDNO, --教師身分證字號
C.NAME, --教師名字
B.CRSNO, --科目代號
B.CRS_NAME,  --科目名稱
decode(TO_CHAR(A.HAND_NUM),'1','第一次','2','第二次','3','二次考察') AS HAND_NUM, --第幾次作業
A.HOMEWORK, --作業檔名
A.SUBJECT_TYPE, --題目類別（1.論文題類 2.非論文題類）
A.CLASS_CODE, --班級代號 
A.CENTER_CODE, --中心代號
D.CENTER_ABRCODE, --中心名稱
( select case when x.mid_edate <= to_char(sysdate,'YYYYMMDD') then 'T' else 'F' end as chk 
from exat020 x where x.ayear = a.ayear and x.sms = a.sms and x.resit_type = '1' ) AS CHK,E.CLASS_KIND 
FROM PERT002 A, COUT002 B, TRAT001 C, SYST002 D ,PLAT012 E 
WHERE A.CRSNO = B.CRSNO  
AND NVL(trim(A.TCH_IDNO),E.TCH_IDNO) = C.IDNO  
AND A.CENTER_CODE = D.CENTER_CODE 
AND A.AYEAR = E.AYEAR 
AND A.SMS = E.SMS 
AND A.CRSNO = E.CRSNO 
AND A.CLASS_CODE = E.CLASS_CODE 
AND D.CENTER_ABRCODE = E.CENTER_ABRCODE  
AND A.AYEAR = '114' --篩選學年
AND A.SMS = '2' --篩選學期
AND C.IDNO = ''  --篩選教師身分證字號
ORDER BY IDNO, CRSNO