Tuesday, October 20, 2009

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

Tuesday, October 13, 2009

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