Travelling Cats

Oracle SQL replace string with another

2012-12-31T19:05:00.001-08:00

example :-



SELECT J.NOSTAF, J.NOKP, J.NOKPR, J.NOKPT, J.NAMA

FROM (SELECT A.NOSTAF, A.NOKP, REPLACE(A.NOKP, ' ', '') AS NOKPR, 

              TRANSLATE(A.NOKP, 'A', 'B') AS NOKPT,

              A.NAMA FROM TANGGUNGAN A) J

WHERE J.NOSTAF LIKE '%';

//REPLACE ([string], [string to replace], [replacement string] )
//TRANSLATE([string], [string to replace], [replacement string] )

with oracle 10g

original data set sample :-



NOSTAF, NOKP, NAMA,

01, A 1111111, ABU

02, A2222222, BAN

03, A3 3 3 3 333, COT



illustrated output :-




NOSTAF NOKP NOKPR NOKPT NAMA   
01 A 1111111 A1111111 B1111111 ABU  
02 A2222222 A2222222 B2222222 BAN  
03 A3 3 3 3333 A3333333 B3333333 COT

Get window (sheet) name (classname) and library in Powerbuilder

2012-12-17T18:42:00.000-08:00

//below function were on clicked event of menu in a mdi...



window lw_active

string ls_winname, ls_libname, ls_wintitle

ClassDefinition cd_windef



lw_active = parentwindow.GetFirstSheet()



if isvalid(lw_active) = True then

        ls_winname = lw_active.classname()

        

        cd_windef = FindClassDefinition(ls_winname)

        ls_libname = cd_windef.LibraryName

        ls_wintitle = lw_active.title

        

        messagebox(ls_winname, ls_libname+'~r~n~r~n'+ls_wintitle, Information!)        

end if

Calling Event with Parameter in Powerscript

2011-11-15T19:52:00.001-08:00

object.event dynamic eventname(parm1, parm2, ...)

sample :

1). calling windows event with paramater,
parent.event dynamic ue_refresh(1)

2). object event dw_1,
dw_1.event dynamic Clicked()

-------------------

Powerbuilder Object Naming Standards

2011-10-05T19:12:00.000-07:00

Listed below, only that i seldom used in my PB application for my quick reference :-
What about the rest?...hmmm...here's a link for more detail,
He's one of my favourite 'Guru'.

OBJECT	PREFIX
ArrayBounds	ab_
CheckBox	cbx_
CommandButton	cb_
Datastore	ds_
Datawindow	dw_
DatawindowChild	dwc_
DragObject	drg_
DropDownListBox	ddlb_
DropDownPictureListBox	ddplb_
dwObject	dwo_
EditMask	em_
Function	f_
Graph	gr_
GroupBox	gb_
HScrollBar	hsb_
Line	li_
ListBox	lb_
ListView	lv_
ListViewItem	lvi_
MailFileDescription	mfd
MailMessage	mm_
MailRecipient	mr_
MailSession	ms_
MDIFrame	mdi_
Menu	m_
MultiLineEdit	mle_
NonVisualObject	nvo_
OLEObject	oo_
Oval	ov
Picture	p_
PictureButton	pb_
PictureListBox	plb_
Pipeline	pl_
RadioButton	rb_
Rectangle	rec_
RichTextEdit	rte_
RoundRectange	rr_
SingleLineEdit	sle_
StaticText	st_
Structure	str_
Tab	tab_
TabPage	tabpage_
Transaction	tr_
Treeview	tv_
TreeviewItem	tvi_
UserObject	uo_
VerticalScrollBar	vsb_
Window	w_
WindowObject	wo_

Powerbuilder Variable Naming Standards - Datatypes

2011-10-04T23:29:00.000-07:00

Variable Declaration prefix Standard :-

argument a
Global g
Instance i
Local l
Shared s

DataType prefix :-

VARIABLE TYPE	PREFIX	GLOBAL	INSTANCE	LOCAL
Any	a	ga_varname	ia_varname	la_varname
Blob	blb	gblb_varname	iblb_varname	lblb_varname
Boolean	b	gb_varname	ib_varname	lb_varname
Character	c	gc_varname	ic_varname	lc_varname
Date	d	gd_varname	id_varname	ld_varname
DateTime	dt	gdt_varname	idt_varname	ldt_varname
Decimal	dec	gdec_varname	idec_varname	ldec_varname
Double	db	gdb_varname	idb_varname	ldb_varname
Integer	i	gi_varname	ii_varname	li_varname
Long	l	gl_varname	il_varname	ll_varname
Real	r	gr_varname	ir_varname	lr_varname
String	s	gs_varname	is_varname	ls_varname
Time	tm	gtm_varname	itm_varname	ltm_varname
Unsigned Integer	ui	gui_varname	iui_varname	lui_varname
Unsigned Long	ul	gul_varname	iul_varname	lul_varname

Oracle SQL Multiple Rows Return One Row

2011-10-02T21:00:00.000-07:00

//Data table DRIVERHP :-

STAFNO | CONTACTNO
---------------------------------
STAF1 11122
STAF1 44455
STAF2 33344
STAF3 66677
STAF3 88888
STAF3 99988

//works on 10g
//the Select Statement 1 :-

SELECT F.STAFNO, RTRIM(XMLAGG(XMLELEMENT(J, F.CONTACTNO,', ')).EXTRACT('//text()'),', ') AS CONTACTNOS
FROM DRIVERHP F
GROUP BY F.STAFNO

//select statement 2 :-
SELECT F.STAFNO, WM_CONCAT(F.CONTACTNO) AS CONTACTNOS
FROM DRIVERHP F
GROUP BY F.STAFNO

//SQL output :-

STAFNO | CONTACTNOS
---------------------------------------------------------------------
STAF1 11122, 44455
STAF2 33344
STAF3 66677, 88888, 99988

Disabling Datawindow Edit

2011-08-16T19:29:00.001-07:00

if you use :-

dw_1.enabled = False //the whole datawindow object will be disabled including scrollbars

instead of that use below :-

dw_1.Object.DataWindow.ReadOnly = "yes"

//the scroll bar will still work and you can scroll up and down the datawindow

to set enable again :_

dw_1.Object.DataWindow.ReadOnly = "no"

Checking SQL statement Status in Powerbuilder

2011-04-17T20:59:00.000-07:00

Assume that your SQL in Powerbuilder script as below :-

//DECLARE sqlca is your transaction...
transaction SQLCA

SELECT * FROM MYTABLE A WHERE A.MYCOL = '01234' using SQLCA;

if SQLCA.sqlcode = 100 then
messagabox('Error', 'Record Not Found',StopSign!)
return
end if

/*
SQLCA.sqlcode = 100 //result not found
SQLCA.sqlcode = 0 //result found or SQL return no error
SQLCA.sqlcode = -1 //returns error in SELECT or INSERT or UPDATE
*/

First Character of Word UpperCase in SQL

2011-04-07T19:09:00.000-07:00

SELECT INITCAP(A.NAME) FROM BIODATA A;

//data example : HARIMADA SABALKUNAN
//SQL output example : Harimada Sabalkunan

Getting Column Data Length with SQL in Oracle

2011-04-07T19:07:00.000-07:00

SELECT * FROM RESULTS A
WHERE LENGTH(A.MATRIXNO) = 8;

//Above SQL will select data from table RESULTS that contains the length of MATRIXNO field equals 8 (eight)

//oracle 9G.

Display as Picture property in Column Datawindow

2010-05-24T19:07:00.000-07:00

//1. your datawindow SQL like below, where the picture name is 00001.jpg, 00002.jpg and so on...

SELECT a.staffno, a.name, ('images\staff\'||a.staffno||'.jpg') as gambar
FROM staff a

//2. the pictures located in subdirectory images\staff\

//3. At column property, check Display as picture

//4. Output of datawindow will be like below...

Using OLE Blob in Powerbuilder 10 using Oracle 10g

2010-04-26T19:07:00.000-07:00

In datawindow, you might want to have OLE column that display such as picture of staff department.
These are the step to do it :-

1. When building datawindow select the ID number (in my case staff number) without selecting the blob / longraw (in Oracle 10g) column.
2. Make sure the table have primary key (staff id), and update properties for datawindow as below :-

3. Add an OLE column from menu Insert->Control->OLE Database Blob.
4. As below the properties for OLE column (select blob column from the same table)

5. Key Clause: [primary key for blob table = :primary key for update in datawindow]
6. Client Name Expression : used when opening paintbrush application to update to which staff number such as below (after running your application)

7. Paintbrush appears when double-click on OLEblob column

8. You can paste a picture or select paste from file...
9. Update back to datawindow by clicking menu in paint such below, then exit paint.

10. When return to datawindow, you have blob picture in you application!
11. Remember to save you datawindow by using dw_1.update() then commit using sqlca;
or the picture will not be updated into your database.
12. This feature doesnt not supported by Appeon till now (version 6.2)

Black Cat Relaxing

2010-04-02T01:59:00.000-07:00

This black cat was found resting at my mother's house. Black is beauty, you're really black uh..

Crosstab and having SQL that display star rating

2010-03-24T20:26:00.000-07:00

//lets say we have this SQL in crosstab datawindow in Powerbuilder (i'm using 10.2.x)

select a.KOD, d.PIHAK , c.JUMBINTANG, c.MULA, c.AKHIR, a.KOD_JENIS, e.SINGKATN, to_char(b.TKH_SEMASA, 'yyyy') as compute_0008_year
from main a, semasa b, star c, pihak d, kdfaklt e
where a.KOD = b.KOD (+)
and b.SEMASA is not null
and a.PIHAK = d.PIHAK (+)
and a.PTJ = e.KDFKLT
having (count(to_char(b.TKH_SEMASA, 'yyyy')) between c.mula and c.AKHIR)
group by a.KOD, d.PIHAK , c.JUMBINTANG, c.MULA, c.AKHIR, a.KOD_JENIS, e.SINGKATN, to_char(b.TKH_SEMASA, 'yyyy')
order by to_char(b.TKH_SEMASA, 'yyyy'), a.KOD_PEKA

//your crosstab definition is like this

//you have datawindow output like this

//but you want an output like below?

//in a dialogbox format property of the displayed number in datawindow code this:-

Powerbuilder Openwithparm

2010-03-11T00:11:00.000-08:00

string ls_stafno
//Open a response window w_find passing a string
ls_stafno = '007'
openwithparm(w_find, ls_stafno, parent)

//other example passing a structure
struc_staff lstrc_staff
lstrc_staff.stafno = '007'
lstrc_staff.name = 'James Bond'
openwithparm(w_find, lstrc_staff)

Connecting Powerbuilder to Oracle 10g

2010-03-10T23:55:00.000-08:00

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

2010-03-09T00:50:00.000-08:00

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

2010-01-18T18:41:00.000-08:00

//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

2010-01-18T18:27:00.000-08:00

//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

2010-01-18T18:08:00.000-08:00

// 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

2010-01-07T00:34:00.000-08:00

//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

2009-11-09T20:39:00.000-08:00

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

2009-10-20T00:47:00.000-07:00

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

2009-10-13T22:21:00.000-07:00

//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

2009-10-13T21:52:00.000-07:00

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

NOSTAF	NOKP	NOKPR	NOKPT	NAMA
01	A 1111111	A1111111	B1111111	ABU
02	A2222222	A2222222	B2222222	BAN
03	A3 3 3 3333	A3333333	B3333333	COT