Wednesday, March 24, 2010

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

No comments: