CONCAT in Oracle

SELECT CONCAT('Name : ', Upper(a.NAME))
FROM BIODATA a
WHERE a.MATRIK = :matrik;

/*Result Sample :-
Name : HARIMADA SABALKUNAN
*/

ISNUMBER in Oracle

Until Oracle 10g doesnt have ISNUMBER function , this is another way to check either the data is a number or not...(until this written, oracle version used is 10g)

SELECT length(translate(trim(column_name),' +-.0123456789',' ')) FROM DUAL;

Will return you NULL if it is a number, Greater than zero if not number
(Actually returns the count of non numeric characters)

In Real Case will compute as :
SELECT A.KODMP,
   B.SETMP,
   decode(length(translate(substr(B.PENSY, 3, 8), ' +-.0123456789', ' ')), null, substr(B.PENSY, 3, 8), B.PENSY) as IS_STAFNO,
   C.0NMSKGR,
   B.SMGRED,
   DECODE((SELECT D.NAMA FROM FASAS D WHERE D.STAFNO = B.PENSY), NULL, 'Tiada',(SELECT D.NAMA FROM FASAS D WHERE D.STAFNO = B.PENSY )) AS NAMA
FROM FMAKK A, FKURPSY B, 0STSMSKGR C
WHERE B.KODMP = A.KODMP
AND B.SMGRED = C.0KMSKGR
AND A.KODFAK like :kodfak
AND B.SESI = :sesi
AND B.SEMESTER = :semester;

SubSelect Statement

SELECT a.IDTEMPAH, a.MATRIK, a.WARGA_TYPE,
decode(a.WARGA_TYPE, 'S', (select b.nama from asas b where b.nostaf = a.matrik), 'P', (select c.nama from biodata c where c.matrik = a.matrik)) as nama,

decode(a.WARGA_TYPE, 'S', (select '('||e.KDFKLT||')'||' '||e.SINGKATN||' - '||e.NAMA from khidmat d, kdfaklt e where d.FAKULTI = e.KDFKLT and d.nostaf = a.matrik),'P',(select '('||f.kodfak||')'||' '||g.singfak||' - '||g.nfakulti from biodata f, fakulti g where f.kodfak = g.kfakulti AND f.matrik = a.matrik )) as ptj,

a.TKH_PERGI, a.TKH_PULANG, a.STATUS, a.LOC_CODE, a.LOC_DESC

FROM TEMPAHAN a
WHERE a.IDTEMPAH = :as_id;

/* one of modified select statement used in one of my project. The first decode used to define WARGA_TYPE either equal to 'S' or 'P'. If 'S' then select nama from asas table else select nama from biodata table
*/

My Superimpossed Image

I have modified this image sometime ago, only the eyes part is mine. Others from street fighters' character named Kyo

Using ROWNUM

//getting first row of result

SELECT a.nostaf, a.nama , b.department
FROM asas, khidmat b
WHERE a.nostaf = b.nostaf
AND ROWNUM = 1;

JOIN SQL

//INNER JOIN
SELECT a.nostaf, a.nama , b.department
FROM asas a INNER JOIN khidmat b
ON a.nostaf = b.nostaf
AND a.nostaf = :stafno;

//OR
SELECT a.nostaf, a.nama , b.department
FROM asas, khidmat b
WHERE a.nostaf = b.nostaf
AND a.nostaf = :stafno;


//NATURAL JOIN
SELECT a.nostaf, a.nama , b.department
FROM asas a NATURAL JOIN khidmat b
WHERE a.nostaf = b.nostaf

CASE SQL

SELECT a.KOD_SUBJECT,
    a.SESI,
    a.SEMESTER,
    a.SKOR,
    c.NAME,
    b.DESCRIPTION,
    c.STAFNO,
    (CASE
     WHEN a.SKOR > 4.49 THEN 'Cemerlang'
     WHEN a.SKOR > 3.99 THEN 'Baik'
     WHEN a.SKOR > 2.99 THEN 'Sederhana'
     ELSE 'Lemah'
    END) as STATUS
FROM SCORES a, JABATAN b, ASAS c
WHERE ( a.KODFAK = b.KODFAK ) AND
    ( a.KODJAB = b.KODJAB ) AND
    ( a.STAFNO = c.STAFNO ) AND
    ( a.STAFNO = :no_staff )

Diz catz is so seriuz

This kitten's picture is taken at the backyard of my inlaw's house using my Nokia N71, isn't he serious. Look at his eyes...

Setting NULL to datawindow column

/*
If it an integer, string, etc, replace with a declaration of the appropriate datatype
*/
datetime ldt_nulldate //to contain null value

SetNull(ldt_nulldate)
if dwo.name = 'b_clear' then
   dw_1.setitem(row, 'mydate', ldt_nulldate)
end if

Using GetSQLSelect( ) & SetSQLSelect( )

//on the event of itemfocuschanged
//declare instance string oldSQL, datawindowchild dwc_dsr

string ls_kw, ls_pt, ls_sp, ls_ak, ls_am, ls_sb, ls_kol, ls_uol
integer li_dasar, li_digit, ret
string whereSQL, newSQL

this.setrow(row)

if dwo.name = 'dasar_id' then
   ls_kw = this.getitemstring(row, "kwg")
   ls_pt = this.getitemstring(row, "ptj")
   ls_sp = this.getitemstring(row, "sptj")
   ls_ak = this.getitemstring(row, "aktv")
   ls_am = this.getitemstring(row, "obam")
   ls_sb = this.getitemstring(row, "obsb")
   ls_kol = this.getitemstring(row, 'kol')
   ls_uol = this.getitemstring(row, 'uol')

   this.getchild('dasar_id', dwc_dsr)
   dwc_dsr.settransobject(sqlca)

   if isnull(oldSQL) or oldSQL = '' then oldSQL = dwc_dsr.getsqlselect();
   whereSQL = '~rAND "BLJ1"."KWG" = ' + "'" + ls_kw + "'"
   whereSQL += '~r AND "BLJ1"."PTJ" = ' + "'" + ls_pt + "'"
   whereSQL += '~r AND "BLJ1"."SPTJ" = ' + "'" + ls_sp + "'"
   whereSQL += '~r AND "BLJ1"."AKTV" = ' + "'" + ls_ak + "'"

   SELECT "KWG"."DIGIT"
   INTO :li_digit
   FROM "KWG"
   WHERE "KWG"."WC01KWG" = :ls_kw using sqlca;

   if sqlca.sqlcode = 0 then
      if li_digit >= 5 then
         whereSQL += '~r AND "BLJ1"."OBAM" = ' + "'" + '0' + "'"
         whereSQL += '~r AND "BLJ1"."OBSB" = ' + "'" + '0' + "'"
         whereSQL += '~r AND "BLJ1"."KOL" = ' + "'" + '0' + "'"
         whereSQL += '~r AND "BLJ1"."UOL" = ' + "'" +'00' + "'"
         whereSQL += '~r AND "BLJ1"."THNBLJ" = ' + string(ii_tahun)

         dwc_dsr.modify( "kodproj.visible=1" )
         dwc_dsr.modify( "vot.visible=0" )
         dwc_dsr.modify( "ptj.visible=0" )
         dwc_dsr.modify( "sptj.visible=0" )
      else
         whereSQL += '~r AND "FIBLJ1"."OBAM" = ' + ls_am
         whereSQL += '~r AND "FIBLJ1"."OBSB" = ' + ls_sb
         whereSQL += '~r AND "FIBLJ1"."THNBLJ" = ' + string(ii_tahun)

         dwc_dsr.modify( "kodproj.visible=0" )
         dwc_dsr.modify( "vot.visible=1" )
         dwc_dsr.modify( "ptj.visible=1" )
         dwc_dsr.modify( "sptj.visible=1" )
      end if
   else
      messagebox('Fail', 'At retrieve digit', StopSign!)
      return
   end if

   newSQL = oldSQL + whereSQL
   ret = dwc_dsr.SetSQLSelect(newSQL)
   dwc_dsr.retrieve()
end if

My Experience with Macromedia

Back in 1999, i was involved in projects that requires presentation using macromedia director and macromedia flash (now known as Adobe). Sharing with you some project screenshot i have done using director :-

All images are created and edited using Photoshop.

Passing Parameter, CloseWithReturn

string ls_bookingno

//Open a response window w_find
open(w_find, parent) //CloseWithReturn(Parent, sle_1.Text), when closing w_find
ls_bookingno = message.stringparm //received string parameter from w_find

if isnull(ls_bookingno) = False and ls_bookingno <> '' then
   sle_id.text = ls_bookingno
   sle_id.triggerevent(Modified!)
end if

/*
message.stringparm :- passes a string or a string variable.
message.doubleparm :- passes a numeric or a numeric variable.
message.powerobjectparm :- passes PB object such as structure or datawindow
*/

PB Datetime Edit Formats

format : dd/mm/yyyy hh:mm AM/PM
output : 10/08/2008 08:48 AM

TO_CHAR Formats

SELECT to_char(SYSDATE, 'dd/mm/yyyy HH:MI') FROM DUAL;
output character: 08/07/2008 04:54

select to_char(CURRENT_DATE, 'dd/mm/yyyy HH:MI') from dual;
output character : 08/07/2008 04:54

SELECT to_char(SYSDATE, 'day/mon/yyyy HH24:MI:SS') FROM DUAL;
output character : thursday/jul/2008 15:34:30

UPDATEBLOB using Powerbuilder

// -------
// ------- usage :doubleclicked objek picture to add, change & update
// ------- thanks to original writer :tropicalbloom -------

integer fh, ret
string txtname, named
string defext = "JPG"
string Filter = "Jpeg Files (*.jpg), *.jpg"
integer li_FileNum, loops, i
long flen, bytes_read, new_pos
blob b, tot_b
string ls_curdir
ulong l_buf

l_buf = 100
ls_curdir = space(l_buf)
ls_curdir = GetCurrentDirectory( )

// Open File directory

ret = GetFileOpenName("Open Jpeg", txtname, named, defext, filter)

// select desired file to DataType BLOB gambar_sf
// Set a wait cursor

SetPointer(HourGlass!)

// Get the file length, and open the file

flen = FileLength(txtname)
li_FileNum = FileOpen(txtname, StreamMode!, Read!, LockRead!)

// Determine how many times to call FileRead

IF flen > 32765 THEN
   IF Mod(flen, 32765) = 0 THEN
      loops = flen/32765
   ELSE
      loops = (flen/32765) + 1
   END IF
ELSE
   loops = 1
END IF

// Read the file
new_pos = 1

FOR i = 1 to loops
   bytes_read = FileRead(li_FileNum, b)
   tot_b = tot_b + b
NEXT

FileClose(li_FileNum)

// blob gambar_sf ; Declare Instance Variables
gambar_sf = tot_b
p_1.SetPicture(gambar_sf)

//use updateblob to update into database

UPDATEBLOB pstaff SET pstaff.spic = :gambar_sf
WHERE pstaff.stafno = :ls_no_staf USING sqlca;

IF sqlca.SQLNRows > 0 THEN
   messagebox('Updated', ls_no_staf)
   COMMIT USING sqlca;
END IF

// returning to Default Directory"
ChangeDirectory (ls_dirname)

SELECTBLOB into picture control

blob lb_pic

SELECTBLOB a.picturecol
INTO :lb_pic
FROM picturetbl a, biodatatbl b
WHERE a.ic = b.ic
AND a.ic = :as_icno using sqlca;

if sqlca.sqlcode = 0 then
   if p_pic.setpicture(lb_pic) = 1 then
      p_pic.visible = True
   else
      p_pic.visible = False
   end if
else
   p_pic.visible = false
end if


/*Above code will select blob from picturetbl and display it on a picture control in Powerbuilder (p_pic)
*/

DECODE and SUBSELECT in Oracle

//DECODE is like if statement if(column, equals, True, False)

SELECT a.bookid, a.warga_type, a.matrik,
decode(a.warga_type, 'S', (select b.nama from fasas b where b.matrik = a.matrik),
'P', (select c.nama from SUN.biodata c where c.matrik = a.matrik)) as nama
FROM BOOKING a
WHERE a.bookid = :as_id

One of my SQL codes (Modified), if WARGA_TYPE = 'S' it will select NAMA from FASAS table.
if WARGA_TYPE = 'P' it will select from table BIODATA for user SUN.

Using Object in Datawindow

To disable a column in datawindow, in PB script write, in open window event or suitable event :

1 - Make the control's disable for editing
0 - Enable for Editing
dw_1.object.objectname.protect = 1
example : dw_detd.object.matrix.protect = 1

To set column background:
0 - Make the control's background opaque.
1 - Make the control's background transparent.

dw_1.object.objectname.background.mode = 0
example : dw_1.object.matrix.background.mode = 0

To set background color dynamically :
dw_1.object.objectname.background.color = RGB(192, 192, 192)
example : dw_1.object.matrix.background.color = RGB(192, 192, 192)

To set text in datawindow :
dw_1.object.objectname.text = 'This is Text'

Using LIKE in SQL

SELECT D.NOSTAF_DRIVER,
D.NODETAIL,
D.TKH_PERGI,
D.TKH_PULANG,
V.VREGNUM,
T.LOC_CODE,
T.LOC_DESC
FROM DETAIL D,
VEHICLE V,
TEMPAHAN T
WHERE ( D.VHCLCODE = V.VCODE ) and
( D.IDTEMPAH = T.IDTEMPAH ) and
( ( D.NOSTAF_DRIVER LIKE '%' ) AND
( TO_CHAR(D.TKH_PERGI, 'YYYY') = '2008' ) AND
( TO_CHAR(D.TKH_PERGI, 'MM') like '01' ) )
ORDER BY D.TKH_PERGI ASC

This SQL will retrieve all data for DETAIL table where NOSTAF_DRIVER equal '%' (all data)

Row Counting Problem in Datawindow for Powerbuilder

Do you want your datawindow report looks like this :

/*
Header Row No :- cumulativesum(1 for all DISTINCT groupname)
Group Row No :- cumulativesum(1 for group 1)
*/

Stripe Color in Datawindow

in datawindow properties > general.color

if (mod (getrow(), 2) = 0, RGB(192,192,192), RGB(255, 255, 255))

or

put a coloured rectangle in datawindow band and send it to back, in visible properties of the rectangle put this code :

if (mod (getrow(), 2) = 0, 1, 0)

Connecting PB to Oracle 9 Database

sqlca.DBMS = "O90 Oracle9i (9.0.1)"
sqlca.ServerName = "server"
sqlca.LogId = "scott"
sqlca.LogPass = "tiger"
sqlca.AutoCommit = False
sqlca.DBParm = "ObjectMode='No',CommitOnDisconnect='No',PBCatalogOwner= 'scott'"

connect using sqlca;

Date Format in SQL for Oracle Database

SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY') FROM DUAL;

output sample: 01/07/2008

SELECT TO_CHAR(SYSDATE, 'MMM/YYYY') FROM DUAL;

output sample: JUL/2008

SELECT TO_CHAR(SYSDATE, 'Dy') FROM DUAL;

output : Sun

SELECT TO_CHAR(SYSDATE, 'Day') FROM DUAL;

output : Sunday

Manual Retrieving DatawindowChild

datawindowchild dwc_year

dw_1.insertrow(0)
dw_1.getchild('year', dwc_year)
dwc_year.settransobject(sqlca)
dwc_year.retrieve('%')

My Story with Powerbuilder

Welcome...

Me & Powerbuilder...

I have been using Powerbuilder for quite sometime since version 5, 8, 9, 10.2 and now 12.1
Well, i am not an expert in Powerbuilder but i just love PB (yup it's a rapid application development -RAD language). For me, i'm just another regular PB programmer. Now i am using PB with Appeon 5, (2011 - now Dec.2012)

These are some of projects i involved/done since i knew Powerbuilder :

1. Attendance System
2. Student Information System
3. Staff Information System

4. Dynamic MenuTree and Security Level Access
5. University Account System
6. Student Account System
7. Budget Information System
8. Vehicle Booking System
9. Bla bla blah...

I will upload my codes in Powerbuilder, SQL from time to time, if have time :)
All the codes are either inspired from my experience or of course PB examples,community,etc. doesn't represent PowerBuilder or other application(s), company or such as if mentioned in this blog. The script maybe correct and it could be errors. I am using it as reference and for sharing.

Regards.