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