Thursday, January 7, 2010

SUBSELECT Advance

//like this also can... thanx to iMohaja

select a.kod_vot, a.NAMA6, a.sumamaun, decode(b.amaun,null,0,b.amaun) amaun,
        (decode(b.amaun ,null,0,  (b.amaun/a.sumamaun)*100)) as perc
from
    (select (decode(sum(k.AMAUN), null, 0, sum(k.AMAUN))) as amaun, k.KOD_VOT, substr(k.KOD_PROJEK,5,2) as ptj
    from projek_kew k
    where substr(k.KOD_PROJEK,1,4) = :thn
    group by (substr(k.KOD_PROJEK,5,2)), K.KOD_VOT) b,
    (select s.KOD_VOT, w.NAMA6, SUM(s.AMAUN) AS SUMAMAUN, s.PTJ
    from budget s, kiobsb w
    where substr(s.KOD_VOT,1,2) = w.OBAM||w.OBSB
    and s.PTJ = :ptj
    and s.THN_BLJ = :thn
    GROUP BY s.KOD_VOT, w.NAMA6, s.ptj) a
where
a.KOD_VOT = b.kod_vot(+)
and a.PTJ = b.ptj(+)
order by 1

No comments: