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

CONCAT in Oracle

SELECT CONCAT('Name : ', Upper(a.NAME))
FROM BIODATA a
WHERE a.MATRIK = :matrik;

/*Result Sample :-
Name : HARIMADA SABALKUNAN
*/

ISNUMBER in Oracle

Until Oracle 10g doesnt have ISNUMBER function , this is another way to check either the data is a number or not...(until this written, oracle version used is 10g)

SELECT length(translate(trim(column_name),' +-.0123456789',' ')) FROM DUAL;

Will return you NULL if it is a number, Greater than zero if not number
(Actually returns the count of non numeric characters)

In Real Case will compute as :
SELECT A.KODMP,
   B.SETMP,
   decode(length(translate(substr(B.PENSY, 3, 8), ' +-.0123456789', ' ')), null, substr(B.PENSY, 3, 8), B.PENSY) as IS_STAFNO,
   C.0NMSKGR,
   B.SMGRED,
   DECODE((SELECT D.NAMA FROM FASAS D WHERE D.STAFNO = B.PENSY), NULL, 'Tiada',(SELECT D.NAMA FROM FASAS D WHERE D.STAFNO = B.PENSY )) AS NAMA
FROM FMAKK A, FKURPSY B, 0STSMSKGR C
WHERE B.KODMP = A.KODMP
AND B.SMGRED = C.0KMSKGR
AND A.KODFAK like :kodfak
AND B.SESI = :sesi
AND B.SEMESTER = :semester;

SubSelect Statement

SELECT a.IDTEMPAH, a.MATRIK, a.WARGA_TYPE,
decode(a.WARGA_TYPE, 'S', (select b.nama from asas b where b.nostaf = a.matrik), 'P', (select c.nama from biodata c where c.matrik = a.matrik)) as nama,

decode(a.WARGA_TYPE, 'S', (select '('||e.KDFKLT||')'||' '||e.SINGKATN||' - '||e.NAMA from khidmat d, kdfaklt e where d.FAKULTI = e.KDFKLT and d.nostaf = a.matrik),'P',(select '('||f.kodfak||')'||' '||g.singfak||' - '||g.nfakulti from biodata f, fakulti g where f.kodfak = g.kfakulti AND f.matrik = a.matrik )) as ptj,

a.TKH_PERGI, a.TKH_PULANG, a.STATUS, a.LOC_CODE, a.LOC_DESC

FROM TEMPAHAN a
WHERE a.IDTEMPAH = :as_id;

/* one of modified select statement used in one of my project. The first decode used to define WARGA_TYPE either equal to 'S' or 'P'. If 'S' then select nama from asas table else select nama from biodata table
*/

My Superimpossed Image

I have modified this image sometime ago, only the eyes part is mine. Others from street fighters' character named Kyo

Using ROWNUM

//getting first row of result

SELECT a.nostaf, a.nama , b.department
FROM asas, khidmat b
WHERE a.nostaf = b.nostaf
AND ROWNUM = 1;

JOIN SQL

//INNER JOIN
SELECT a.nostaf, a.nama , b.department
FROM asas a INNER JOIN khidmat b
ON a.nostaf = b.nostaf
AND a.nostaf = :stafno;

//OR
SELECT a.nostaf, a.nama , b.department
FROM asas, khidmat b
WHERE a.nostaf = b.nostaf
AND a.nostaf = :stafno;


//NATURAL JOIN
SELECT a.nostaf, a.nama , b.department
FROM asas a NATURAL JOIN khidmat b
WHERE a.nostaf = b.nostaf