SELECT b.MATRIK, b.NAMA, a.SESI, a.SEMESTER,
a.KDKOM, a.CPA, a.KSTP, b.KLULUS, b.PROGBARU,
(SELECT decode(sum(c.K), null, 0, sum(c.K)) FROM FBK c
WHERE c.MATRIK = b.MATRIK) as kpindah,
(select e.KDKOM from FPNGK e where e.MATRIK = b.MATRIK and e.SESI = a.SESI
and e.SEMESTER = a.SEMESTER) as kdsemlepas,
(select decode(sum(d.K), null, 0, sum(d.K)) from FAMBK d
where d.SESI = a.SESI and d.SEMESTER = a.SEMESTER and d.MATRIK = b.MATRIK) as ksemini,
(case
when a.SEMESTER = '2' then
(select e.KDKOM from FPNGK e where e.MATRIK = b.MATRIK and e.SESI = :sesi
and e.SEMESTER = '1')
when a.SEMESTER = '1' then
(select e.KDKOM from FPNGK e where e.MATRIK = b.MATRIK and e.SESI =
to_char(to_number(:sesi)-10001) and e.SEMESTER = (decode((select e.MATRIK from FPNGK e
where e.MATRIK = b.MATRIK and e.SESI = to_char(to_number(:sesi)-10001)
and e.SEMESTER = '3'), NULL, '2', '3')))
when a.SEMESTER = '3' then
(select e.KDKOM from FPNGK e where e.MATRIK = b.MATRIK and e.SESI = :sesi
and e.SEMESTER = '2')
end ) as kdsemlepas
FROM FPNGK a,
FBIODATA b
WHERE b.MATRIK = a.MATRIK
AND a.SESI = :sesi
AND a.SEMESTER = :sem
AND b.THPPGN like :tahap
AND b.STATUS = 'A';
// note :- sesi = '20082009', sem = '1', tahap = 'B' / 'D' (Bachelor/Diploma)
