其他 顯示系統設定的選修科目

你好，
我成功能使用以下SQL顯示每班有甚麼科目，其divsubjcode（若有）, 任教老師shortname, 當中包括必修科目（中英數通）及跨班別組別科目cross class subject（所有高中選修科）, 但未能顯示選修科目，請問可以如何修改？我指的選修科目，不是高中的選修科，而是在系統設定的選修科目。
謝謝！


select
a.classcode 'class',
c.EN_SNAME 'subkey', e.divsubjcode 'e_subkey',
(case when b.clssubjtype='X' then e2.shortname
when b.clssubjtype='G' then g2.shortname
else d2.shortname end) 'tid'
from tb_stu_stusubj b
join (select suid, stuid, schyear, schlvl, schsess, classlvl, classcode, subjgroup, max(stuschrecid) 'stuschrecid' from vw_stu_lateststudent group by suid, stuid, schyear, schlvl, schsess, classlvl, classcode, subjgroup) a
on a.suid=b.suid and a.stuid=b.stuid and a.stuschrecid=b.stuschrecid
left outer join tb_hse_common c
on b.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
join wsadmin.TB_HSE_COMMON c1
on b.SUID = c1.SUID and c1.TB_ID = 'MOI' and b.MOI = c1.CODE_ID
left outer join tb_sch_clssubjtchr d
on a.suid=d.suid and a.schlvl=d.schlevel and a.schsess=d.schsession and a.schyear=d.schyear and a.classlvl=d.classlevel and a.classcode=d.classcode and b.moi=d.moi and b.subjcode=d.subjcode
left outer join wsadmin.VW_ASR_STAFF d2
on d.SUID = d2.SUID and d.STAFFCODE = d2.STAFFCODE
left outer join tb_sch_dsgsubjtchr e
on a.suid=e.suid and a.schlvl=e.schlevel and a.schsess=e.schsession and a.schyear=e.schyear and b.crosssubjgrp=e.subjgrpcode and b.crosssubjcode=e.divsubjcode
left outer join wsadmin.VW_ASR_STAFF e2
on e.SUID = e2.SUID and e.STAFFCODE = e2.STAFFCODE
left outer join tb_sch_esgsubjtchr g
on a.suid=g.suid and a.schlvl=g.schlevel and a.schsess=g.schsession and a.schyear=g.schyear and a.classlvl=g.classlevel and a.subjgroup=g.subjgrpcode and b.subjcode=g.subjcode
left outer join wsadmin.VW_ASR_STAFF g2
on g.SUID = g2.SUID and g.STAFFCODE = g2.STAFFCODE
where a.schyear=?
group by a.schyear, a.classlvl, a.classcode, b.clssubjtype, c1.ch_des, b.subjcode, c.ch_des,c.en_des, c.EN_SNAME,c.ch_sname, b.crosssubjcode, d.staffcode, e.staffcode, g.staffcode, d2.NAME_CHI, e2.NAME_CHI, g2.NAME_CHI, d2.NAME_eng, e2.NAME_eng, g2.NAME_eng, d2.shortname, e2.shortname, g2.shortname,e.divsubjcode
order by a.classlvl, a.classcode, b.subjcode

 

老師所提供的SQL已經過測試
可以正常顯示選修科目
請老師提供資料有甚麼選修科不能顯示以及該選修科的設定

 

回覆 2# edb-escm


我們設定, 5d, 6d班有數學m1及m2, 學生選修其一。m1(23s, divsubjectcode=mam1), m2(24s, divsubjectcode=mam2)。
2014年, 我們設定該科為必修科目, 以上sql能正常顯示, subject type為"C"。今年, 我們設定為選修科目, 但以上SQL未能見到有關科目。

請協助, 謝謝!

 

經過測試
Subject type為C,E,G或X都能提取
建議老師聯絡貴校的學校聯絡主任以便跟進