extract students' services and activiy for eclass

Dear sir,

I would like to know how to extract the following information for data import of eclass.

Activity:

Column 1: The School Year of the record (You can leave it empty if the record is current school year, system will interpret empty as current school year which is set in Basic Settings).
Column 2: The Semester of the record (You can leave it empty if the record is current semester, system will interpret empty as current semester, which is set in Basic Settings).
Column 3: WebSamsRegNo (The number starts with # e.g: #123456)
Column 4: Activity Name.
Column 5: Role (optional).
Column 6: Performance (optional).
Column 7: Remark of the record (optional).
Column 8: Organization holding the activity (optional)

Services

Column 1: The School Year of the record (You can leave it empty if the record is current year, system will interpret empty as current academic year, which is set in Basic Settings).
Column 2: The Semester of the record (You can leave it empty if the record is current semester, system will interpret empty as current semester, which is set in Basic Settings).
Column 3: Date of the record (YYYY-MM-DD) (optional).
Column 4: WebSamsRegNo (The number starts with # e.g: #123456)
Column 5: Service Name.
Column 6: Role (optional).
Column 7: Performance (optional).
Column 8: Remarks of the record (optional).
Column 9: Organization holding the service (optional).

Thanks!

Ck Tse

 

Please try the below SQL which would extract the data of Activities and Services.

select
   a.schyear,
   a.duration,
   '#' || s.regno 'RegNo',
   c.ch_des 'Activities/Services Name',  
   d.ch_des 'Post',
   e.ch_des 'Performance',
   (case when a.naatype='E' then 'Activities'
            when a.naatype='S' then 'Services' end) 'Type'
from wsadmin.vw_stu_lateststudent s
join wsadmin.tb_naa_studentnaa b
   on s.suid=b.suid and s.stuid=b.stuid
join wsadmin.tb_naa_naaoffered a
   on a.suid=b.suid and a.naaofferedid=b.naaofferedid and s.schyear=a.schyear
left outer join wsadmin.tb_hse_common c
   on a.suid=c.suid and a.naacode=c.code_id and ((c.tb_id='ECACD' and a.naatype='E') or (c.tb_id='SEVPST' and a.naatype='S'))
left outer join wsadmin.tb_hse_common d
   on a.suid=d.suid and b.naapostcode=d.code_id and d.tb_id='ECAPST'
left outer join wsadmin.tb_hse_common e
   on a.suid=e.suid and b.naaperfcode=e.code_id and e.tb_id='ECAPFM'
where s.schyear=? and a.naatype in ('E', 'S')
order by a.naatype, s.regno

 

Thanks!  But the "case ... end" statement doesn't work.  I just simply changed it to show a.naatype instead!