Home » Developer & Programmer » Reports & Discoverer » peculiar problem when pramaterize the report? (report 6i)
peculiar problem when pramaterize the report? [message #337259] Wed, 30 July 2008 05:39 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi
I have query which applied with static value im getting output in report very fast

Select  ap_documentno, ap_documentdate,glcode,a.vendcode,vendname,
vendbillno,vendbilldt,billvalue,ponumber,poamendmentno,
db_ref_number,e.grinno,db_amount,cr_ref_number,cr_amount,ap_amount,excisedutyamt,cessamount,salestaxamt,a.TDS_AMOUNT
from billmain a,billitem b,ledger c,vendmast d,grinmain e
where a.ap_documentno between '82060001' and '82060964'
  and a.unitid = b.unitid
  and a.periodid = b.periodid
  and a.documentno = b.documentno
  and a.ap_documentno = c.documentno
  and a.ap_documentdate = c.documentdate
  and a.unitid = c.unitid
  and a.periodid = c.periodid
  and a.vendcode = d.vendcode
  and a.unitid = e.unitid
  and b.unitid = e.unitid
  and c.unitid = e.unitid
  and a.ap_documentno = e.cn_refno
  and c.glcode in (select distinct glcode from partgroup)
  and a.unitid = 1
  and a.periodid = 13
order by ap_documentno;


when i try to parameterize the report the query below.

Select distinct ap_documentno, ap_documentdate,glcode,a.vendcode,vendname,
vendbillno,vendbilldt,billvalue,ponumber,poamendmentno,
db_ref_number,e.grinno,Nvl(db_amount,0) dbamt,cr_ref_number,Nvl(cr_amount,0) cramt ,nvl(ap_amount,0) apamt, nvl(excisedutyamt,0) edamt , nvl(cessamount,0) cessamt,nvl(salestaxamt,0) taxamt , nvl(a.TDS_AMOUNT,0) tdsamt
from billmain a,billitem b,ledger c,vendmast d,grinmain e
where a.ap_documentno between :P_fromno and :P_tono
  and a.unitid = b.unitid
  and a.periodid = b.periodid
  and a.documentno = b.documentno
  and a.ap_documentno = c.documentno
  and a.ap_documentdate = c.documentdate
  and a.unitid = c.unitid
  and a.periodid = c.periodid
  and a.vendcode = d.vendcode
  and a.unitid = e.unitid
  and b.unitid = e.unitid
  and c.unitid = e.unitid
  and a.ap_documentno = e.cn_refno
  and c.glcode in (select distinct glcode from partgroup)
  and a.unitid = :P_unitid
  and a.periodid = :P_periodid
order by ap_documentno;


Even for single ap_documentno im getting too long time.
If i go greate than 100 and too much delay

Anybody can u explain why it happens.

kanish
Re: peculiar problem when pramaterize the report? [message #340504 is a reply to message #337259] Wed, 13 August 2008 02:33 Go to previous message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Try It


Select distinct ap_documentno, ap_documentdate,glcode,a.vendcode,vendname,
vendbillno,vendbilldt,billvalue,ponumber,poamendmentno,
db_ref_number,e.grinno,Nvl(db_amount,0) dbamt,cr_ref_number,Nvl(cr_amount,0) cramt ,nvl(ap_amount,0) apamt, nvl(excisedutyamt,0) edamt , nvl(cessamount,0) cessamt,nvl(salestaxamt,0) taxamt , nvl(a.TDS_AMOUNT,0) tdsamt
from billmain a,billitem b,ledger c,vendmast d,grinmain e
where a.ap_documentno between :P_fromno and :P_tono
and a.unitid = b.unitid
and a.periodid = b.periodid
and a.documentno = b.documentno
and a.ap_documentno = c.documentno
and a.ap_documentdate = c.documentdate
and a.unitid = c.unitid
and a.vendcode = c.slcode
and a.periodid = c.periodid
and a.vendcode = d.vendcode
and a.unitid = e.unitid
and b.unitid = e.unitid
and c.unitid = e.unitid
and a.ap_documentno = e.cn_refno
and c.glcode in (select distinct glcode from partgroup)
and a.unitid = :P_unitid
and a.periodid = :P_periodid
order by ap_documentno;
Previous Topic: Reports Download
Next Topic: Calling a runtime report from a procedure
Goto Forum:
  


Current Time: Fri Dec 02 20:21:04 CST 2016

Total time taken to generate the page: 0.08476 seconds