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