Connecting Powerbuilder to Oracle 10g

sqlca.DBMS = "O10 Oracle10g (10.1.0)"
sqlca.LogPass = "tiger"
sqlca.ServerName = "server"
sqlca.LogId = "scott"
sqlca.AutoCommit = False
sqlca.DBParm = "CommitOnDisconnect='No',Async=1,PBCatalogOwner='scott'"

connect using sqlca;
dw_1.settransobject(sqlca)

More CASE and DECODE in oracle SQL

SELECT b.TARIKH,
b.KOD_PROJEK,
a.NAMA_PROJEK,
b.KOD_VOT, 
b.DASAR,
b.KET,
(select d.NAMA_TRANS from sbp_trans d where d.JENIS = b.JENIS_TRANS) as trans,
b.LO as rujukan,
(case when b.jenis_trans in (3,4) then b.amaun
else null
end) as debit,
DECODE(b.JENIS_TRANS, 5, b.AMAUN, null) as kredit,
(case when b.jenis_trans not in (1,2,6) then null
else b.AMAUN
end) as amt_col8, 
b.JENIS_TRANS,
(select c.AMAUN from sbp_budget c where c.KOD_VOT = b.kod_vot and c.DASAR = b.dasar and c.PTJ = a.PTJ and c.SPTJ = a.SPTJ
and c.thn_blj = :as_thnblj and c.JENIS = 1) as peruntukan,
b.KOD_SODO
FROM PROJEK a, BELANJA b
WHERE b.KOD_VOT like :as_vot
AND b.DASAR like :as_dasar
AND a.KOD_PROJEK = b.KOD_PROJEK
AND substr(a.KOD_PROJEK, 1,4) = :as_thnblj
AND a.PTJ = :as_ptj
AND a.SPTJ like :as_sptj

Enable/Disable column editing in datawindow in rowfocuschanged script

//in rowfocuschanged event in datawindow, write this

long ll_budget

if currentrow > 0 then

ll_budget = this.getitemnumber(currentrow, 'expense')
  
    if ll_budget > 0 then
        this.object.kod_vot.protect = 1   //kod_vot is your column_name
        this.object.dasar.protect = 1       //dasar is your column_name
    else
        this.object.kod_vot.protect = 0
        this.object.dasar.protect = 0
    end if
end if

//this will disable editing if ll_budget is greater than 0 and enable editing if it is zeroin other way you can always do this in datawindow expression.
go to protect expression for the particular column then code this :-

if (budget > 0, 1, 0)

//means when budget value greater than 0, 1 = to protect (disable column edit) else 0 = enabled column edit.

To Enable/disable Editing on new/existing row in datawindow

 //In your datawindow write this code in your column property, in ->General->protect

if (isRowNew(), 0, 1)

//this will enable editing on newrow and disable editing on existing row

//write code in each column to enable/disable editing for a row...

//you can put other conditions like calculation such as

if (columnname = 'Admin', 0, 1)

Having in Select Statement

 // working sample from one of my project that using HAVING select statement...

select a.KOD_PEKA, d.PIHAK , c.BINTANG, c.MULA, c.AKHIR
from p_main a, p_semasa b, p_star c, p_pihak d
where a.KOD_PEKA = b.KOD_PEKA (+)
and b.SEMASA is not null
and to_char(b.TKH_SEMASA, 'yyyy') = '2009'
and a.KOD_PIHAK = d.KOD_PIHAK (+)
having  (count(to_char(b.TKH_SEMASA, 'yyyy')) between c.mula and c.AKHIR)
group by a.KOD_PEKA, d.PIHAK , c.BINTANG, c.MULA, c.AKHIR
order by a.KOD_PEKA

SUBSELECT Advance

//like this also can... thanx to iMohaja

select a.kod_vot, a.NAMA6, a.sumamaun, decode(b.amaun,null,0,b.amaun) amaun,
        (decode(b.amaun ,null,0,  (b.amaun/a.sumamaun)*100)) as perc
from
    (select (decode(sum(k.AMAUN), null, 0, sum(k.AMAUN))) as amaun, k.KOD_VOT, substr(k.KOD_PROJEK,5,2) as ptj
    from projek_kew k
    where substr(k.KOD_PROJEK,1,4) = :thn
    group by (substr(k.KOD_PROJEK,5,2)), K.KOD_VOT) b,
    (select s.KOD_VOT, w.NAMA6, SUM(s.AMAUN) AS SUMAMAUN, s.PTJ
    from budget s, kiobsb w
    where substr(s.KOD_VOT,1,2) = w.OBAM||w.OBSB
    and s.PTJ = :ptj
    and s.THN_BLJ = :thn
    GROUP BY s.KOD_VOT, w.NAMA6, s.ptj) a
where
a.KOD_VOT = b.kod_vot(+)
and a.PTJ = b.ptj(+)
order by 1

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