Oracle SQL replace string with another

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

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

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

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

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

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

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

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

SELECT INITCAP(A.NAME) FROM BIODATA A;

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

Getting Column Data Length with SQL in Oracle

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

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