Monday, November 9, 2009

More SQL decode, to_char, to_number, case in oracle 10g

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)