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)

SUBSTR in Oracle

Let say we have a data like below in a table :-

ABC12345

We can abstract data using

SUBSTR([column],[position from left],[length]) such as below :-

SELECT SUBSTR(a.mycolumn, 4, 5) FROM mytable a;

will result an output :- 12345

Dynamic runtime modifying SQL in datawindow

//let say we have these codes in open window event

string ls_code, ls_SQL, ls_newSQL

ls_code = message.stringparm
dw_1.settransobject(sqlca)

//get existing SQL from dw_1
ls_SQL = dw_1.getsqlselect( )

//dynamicly modify SQL in datawindow by adding where statement...
if ls_code = '-' then
    ls_newSQL = ls_SQL + " where b.KOD_JENIS IS NULL order by b.KOD_PEKA"
else
    ls_newSQL = ls_SQL + " where b.KOD_JENIS = '"+ls_code+"' order by b.KOD_PEKA"
end if

//sets new SQL to dw_1
dw_1.setsqlselect(ls_newSQL)
dw_1.retrieve()

/* example that you have this SQL in dw_1

select b.KOD, b.KOD_JENIS,
       (select c.PIHAK from ppihak c where c.KOD_PIHAK = b.KOD_PIHAK) as pihak,
       b.KOD_PTJ, b.TKH_TTGN, b.TKH_TAMAT, b.TKH_UPDATE, b.ID_UPDATE
from pmain b

*/

Starteam problem starting in PB10

Once i have problem loading starteam when i started my PB10, when select to 'starteam source control' and try to connect, PB10 crashed. After adding some command in pb.ini, problem solved. Didnt remember where got this solution, but it is either from Borland or Sybase forum.

Add below command  into your pb.ini :-

[JavaVM]
CreateJavaVM=0

----------------------------------------------------------------------
(usually pb.ini found in path like this C:\Program Files\Sybase\PowerBuilder 10.0)

i'am using Borland Starteam 2008 released 2 with PB10.2

Using UNION in Oracle

Union will combine two sets of data, see example below :)

select a.KOD_JENIS, a.NAMA_JENIS, count(b.KOD_JENIS)
from ptype a, pmain b
where a.KOD_JENIS = b.KOD_JENIS
group by a.KOD_JENIS, a.NAMA_JENIS
union
select '-', 'No Data', count(*)
from pmain b
where b.kod_jenis is null