select
DECODE (TO_CHAR (sysdate, 'MM'),
'01', 'JANUARI',
'02', 'FEBRUARI',
'03', 'MAC',
'04', 'APRIL',
'05', 'MEI',
'06', 'JUN',
'07', 'JULAI',
'08', 'OGOS',
'09', 'SEPTEMBER',
'10', 'OKTOBER',
'11', 'NOVEMBER',
'12', 'DISEMBER'
)||(' ')||
TO_CHAR (sysdate, 'YYYY') sysd,
mo.ID_MOHON ,
mo.PENYERAH_NAMA,
mo.PENYERAH_ALAMAT1,
mo.PENYERAH_ALAMAT2,
mo.PENYERAH_ALAMAT3,
mo.PENYERAH_ALAMAT4,
mo.PENYERAH_POSKOD,
kn.NAMA penyerah_kod_negeri,
ha.KOD_HAKMILIK,
ha.NO_LOT,
kb.NAMA kod_bpm_nama,
kd.NAMA kod_daerah_nama,
--le.TRH_LELONG,
le.TMPT,
pi.NAMA pihak_nama,
pi.ALAMAT1 pihak_alamat1,
pi.ALAMAT2 pihak_alamat2,
pi.ALAMAT3 pihak_alamat3,
pi.ALAMAT4 pihak_alamat4,
pi.POSKOD pihak_poskod,
pi.KOD_NEGERI pihak_kod_negeri,
ju.NAMA jurulelong_nama,
ju.ALAMAT1 jurulelong_alamat1,
ju.ALAMAT2 jurulelong_alamat2,
ju.ALAMAT3 jurulelong_alamat3,
ju.ALAMAT4 jurulelong_alamat4,
ju.POSKOD jurulelong_poskod,
ju.KOD_NEGERI jurulelong_kod_negeri,
mp.KOD_PIHAK,
to_char(le.trh_lelong,'DD')||' '
||DECODE (TO_CHAR (le.trh_lelong, 'MM'),
'01', 'JANUARI',
'02', 'FEBRUARI',
'03', 'MAC',
'04', 'APRIL',
'05', 'MEI',
'06', 'JUN',
'07', 'JULAI',
'08', 'OGOS',
'09', 'SEPTEMBER',
'10', 'OKTOBER',
'11', 'NOVEMBER',
'12', 'DISEMBER'
)||(' ')||
TO_CHAR (le.trh_lelong, 'YYYY') trh_lelong,
to_char(le.TRH_lelong,'HH12:MI ') hour,
DECODE(SUBSTR(to_char(le.TRH_lelong,'HH12:MI AM'),-2,2),'AM','Petang','pagi') noon,
le.HARGA_RIZAB
from
mohon mo,
mohon_hakmilik mh,
hakmilik ha,
kod_bpm kb,
kod_daerah kd,
lelong le,
enkuiri en,
jurulelong ju,
mohon_pihak mp,
pihak pi,
kod_negeri kn
where
mo.id_mohon=:p_id_mohon
and mo.ID_MOHON=mh.ID_MOHON
and mh.id_hakmilik=ha.ID_HAKMILIK
and ha.KOD_BPM=kb.KOD
and ha.KOD_DAERAH=kd.KOD
and mo.ID_MOHON=en.ID_MOHON
and en.ID_ENKUIRI=le.ID_ENKUIRI
and le.ID_JLB=ju.ID_JLB
and mo.ID_MOHON=mp.ID_MOHON
--and mp.KOD_PIHAK='PG'
and mp.ID_PIHAK=pi.ID_PIHAK
and mo.PENYERAH_KOD_NEGERI=kn.KOD
this is my main query and the output for it is
ID_MP,SYSD,ID_MOHON,PENYERAH_NAMA,PENYERAH_ALAMAT1,PENYERAH_ALAMAT2,PENYERAH_ALAMAT3,PENYERAH_ALAMAT4,PENYERAH_POSKOD,PENYERAH_KOD_NEGERI,KOD_HAKMILIK,NO_LOT,KOD_BPM_NAMA,KOD_DAERAH_NAMA,TMPT,PIHAK_NAMA,PIHAK_ALAMAT1,PIHAK_ALAMAT2,PIHAK_ALAMAT3,PIHAK_ALAMAT4,PIHAK_POSKOD,PIHAK_KOD_NEGERI,JURULELONG_NAMA,JURULELONG_ALAMAT1,JURULELONG_ALAMAT2,JURULELONG_ALAMAT3,JURULELONG_ALAMAT4,JURULELONG_POSKOD,JURULELONG_KOD_NEGERI,ID_PIHAK,KOD_PIHAK,TRH_LELONG,HOUR,NOON,HARGA_RIZAB
21952,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,242,Bandar Seremban,Seremban,PEJABAT TANAH,BANK ISLAM MALAYSIA BERHAD,JALAN SAMANTAN,TAMAN BUNGA,MELAKA TENGAH,null,86400,04,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,364124,PG,14 SEPTEMBER 2010,04:20 ,pagi,12
21952,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,0004619,Mukim Jimah,Seremban,PEJABAT TANAH,BANK ISLAM MALAYSIA BERHAD,JALAN SAMANTAN,TAMAN BUNGA,MELAKA TENGAH,null,86400,04,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,364124,PG,14 SEPTEMBER 2010,04:20 ,pagi,12
1234,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,0004619,Mukim Jimah,Seremban,PEJABAT TANAH,HJ SAMEON BIN MURAT,NO.07,JALAN KG URAP,KAMPUNG LAMA,JADI BARU,null,86500,04,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,3243543,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
1234,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,242,Bandar Seremban,Seremban,PEJABAT TANAH,HJ SAMEON BIN MURAT,NO.07,JALAN KG URAP,KAMPUNG LAMA,JADI BARU,null,86500,04,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,3243543,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
21950,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,0004619,Mukim Jimah,Seremban,PEJABAT TANAH,HJ SAMEON BIN MURAT,NO.07,JALAN KG URAP,KAMPUNG LAMA,JADI BARU,null,86500,04,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,3243543,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
21950,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,242,Bandar Seremban,Seremban,PEJABAT TANAH,HJ SAMEON BIN MURAT,NO.07,JALAN KG URAP,KAMPUNG LAMA,JADI BARU,null,86500,04,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,3243543,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
23434,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,0004619,Mukim Jimah,Seremban,PEJABAT TANAH,MOHD NUR FIKRI BIN ABD GHANI,no 4,lorong damak 4,jalan indah 4,null,44444,03,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,22886050,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
21951,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,0004619,Mukim Jimah,Seremban,PEJABAT TANAH,MOHD NUR FIKRI BIN ABD GHANI,no 4,lorong damak 4,jalan indah 4,null,44444,03,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,22886050,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
23434,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,242,Bandar Seremban,Seremban,PEJABAT TANAH,MOHD NUR FIKRI BIN ABD GHANI,no 4,lorong damak 4,jalan indah 4,null,44444,03,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,22886050,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
21951,NOVEMBER 2010,0405AUC2010007133,FIRDAUS AZLINA & CO,SUITE 9B.01,LEVEL 9B,WISMA E & C, NO 2,LORONG DUNGUN KIRI,DAMANSARA HEIGHTS,50490,WP Kuala Lumpur,HSM,242,Bandar Seremban,Seremban,PEJABAT TANAH,MOHD NUR FIKRI BIN ABD GHANI,no 4,lorong damak 4,jalan indah 4,null,44444,03,NUR,NO.195,JALAN RAYE,TAMAN BUNGE,AYER KEROH,MELAKA,86400,04,22886050,PM,14 SEPTEMBER 2010,04:20 ,pagi,12
here is my report layout
Ruj. Tuan :
Ruj. Kami :&<id_mohon>
Tarikh :&<sysd>
&<cf_penyerah_addr>
Permohonan Perintah Jual.
&<kod_hakmilik> &<no_lot> &<kod_bpm_nama> Daerah &<kod_daerah_nama>.
Adalah saya dengan hormatnya meaklumkan bahawa jualan Lelongan Awam telah ditetapkan pada &<sysd> iam &<hour> &<noon> di &<tmpt> dengan harga rezab &<harga_rizab>
Pelelong Berlesen yang dilantik adalah seperti berikut:
&<cf_lelong_addr>
Sekian dimaklumkan, terima kasih.
"BERKHIDMAT UNTUK NEGARA"
Saya yang menurut perintah,
(RAJA BADERUL SHAH BIN RAJA SALIM)
Penolong Pentadbir Tanah
b.p.&<kod_daerah_nama>
&<cf_pihak_addr_pg>
iam using one main frame in that iam using one repeater frame inside repeater frame using text i designed this report
&<cf_pihak_addr_pg>
function CF_pihak_addr_pg Formula return Char is
v_address varchar2(2000);
begin
if :kod_pihak='PG' THEN
--select
v_address := :pihak_nama ||','|| chr(10)
|| CASE WHEN :pihak_alamat1 IS NOT NULL THEN :pihak_alamat1 ||','|| chr(10) END
|| CASE WHEN :penyerah_alamat2 IS NOT NULL THEN :pihak_alamat2 ||','|| chr(10) END
|| CASE WHEN :pihak_alamat3 IS NOT NULL THEN :pihak_alamat3 ||','|| chr(10)END
|| CASE WHEN :pihak_alamat4 IS NOT NULL THEN :pihak_alamat4 ||','|| chr(10)END
|| CASE WHEN :pihak_poskod IS NOT NULL THEN :pihak_poskod || ',' END
||CASE WHEN :pihak_kod_negeri IS NOT NULL THEN :pihak_kod_negeri END;
END IF;
return v_address;
end;
finally what i need is
according to the above main query output and my cf_pihak_addr_pg i need to generate 1 report based on id_pihak and kod_pihak
and the address for cf_pihak_addr_pg should be display in the same report twice one after the other .i order to do so whether
i need to place this &<cf_pihak_addr_pg> in one more repeating frame inside the existing repeating frame..
please help me to do so
Edited by: user9093689 on Nov 9, 2010 2:04 AM