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

Using OLE Blob in Powerbuilder 10 using Oracle 10g

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

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

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

 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

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