Oracle Reports
-
Hello friends,
I'm using the under mentioned query in report to find the values .I want sum of prod_type_code grouped according to client_id then z_create_user_id and then through whole report.I'm using three formula columns to do this task and using same base query with slight modifications.But I want this to be done in a single query as this is taking too much time to execute.Kindly help.Can I achieve this using Oracle Analytic Functions.
SELECT DISTINCT a.z_create_user_id,b.client_id,
b.hs_id,cmpgn_code,b.hs_Gen_seq,b.hs_sub_seq, b.eff_date, a.notice_date,
prod_type_code product_type, a.cx_rsn_code,
b.tot_prem_price, crmp10385(b.acct_gen_seq,b.eff_date,b.exp_date) paid, RNWL_CALL_DISP_CODE
FROM hs_cx_rsn a, hs_old b, ( SELECT hist.hs_gen_seq, call.RNWL_CALL_DISP_CODE
FROM commn_hist_log_old hist, rnwl_call_log call
WHERE hist.COMMN_HIST_LOG_GEN_SEQ = call.COMMN_HIST_LOG_GEN_SEQ ) c,client_cmpgn_cell d
WHERE a.hs_gen_seq = b.hs_gen_seq
and b.hs_gen_seq = c.hs_gen_seq(+)
and trunc(a.z_create_date) between :in_start_date and :in_end_date
and ( a.CX_RSN_CODE != 'RVSOFR' AND
(a.override_date is null or a.override_date > trunc(a.z_create_date) + 30)
)
and a.z_create_user_id in (select assc_id from thrd_pty_comm_assc)
and (a.z_create_user_id = :in_assoc_id or :in_assoc_id='ALL')
and d.client_id = b.client_id
and d.cell_code = b.cell_code
and (d.cmpgn_code=:in_cmpgn_code or :in_cmpgn_code='ALL')
order by a.z_create_user_id, b.client_id, b.hs_id,b.hs_sub_seq
-
Maybe if you give us a simplified schema and a sample of the desired output we could help... Your query is pretty confusing.
-
<FONT face="Times New Roman" size=3>I’m providing you the snapshot of the problem.</FONT>
<FONT size=3><FONT face="Times New Roman"> <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></FONT></FONT>
<FONT face="Times New Roman" size=3>I’ve to create a Report using Oracle Report Builder (.RDF)</FONT>
<FONT face="Times New Roman" size=3>In this report I’ve to calculate the total of product_type at three different levels.</FONT>
<FONT face="Times New Roman" size=3>First at report level then at z_create_user_id and then at client_id.</FONT>
<FONT size=3><FONT face="Times New Roman"> <o:p></o:p></FONT></FONT>
<FONT face="Times New Roman" size=3>SELECT DISTINCT a.z_create_user_id,b.client_id,</FONT>
<FONT face="Times New Roman" size=3>b.hs_id,cmpgn_code,b.hs_Gen_seq,b.hs_sub_seq, b.eff_date, a.notice_date,</FONT>
<FONT face="Times New Roman" size=3>prod_type_code product_type, a.cx_rsn_code,</FONT>
<FONT face="Times New Roman" size=3>b.tot_prem_price, crmp10385(b.acct_gen_seq,b.eff_date,b.exp_date) paid, RNWL_CALL_DISP_CODE</FONT>
<FONT face="Times New Roman" size=3>FROM hs_cx_rsn a, hs_old b, ( SELECT hist.hs_gen_seq, call.RNWL_CALL_DISP_CODE</FONT>
<FONT size=3><FONT face="Times New Roman"> FROM commn_hist_log_old hist, rnwl_call_log call</FONT></FONT>
<FONT size=3><FONT face="Times New Roman"> WHERE hist.COMMN_HIST_LOG_GEN_SEQ = call.COMMN_HIST_LOG_GEN_SEQ ) c,client_cmpgn_cell d</FONT></FONT>
<FONT face="Times New Roman" size=3>WHERE a.hs_gen_seq = b.hs_gen_seq </FONT>
<FONT face="Times New Roman" size=3>and b.hs_gen_seq = c.hs_gen_seq(+)</FONT>
<FONT face="Times New Roman" size=3>and trunc(a.z_create_date) between :in_start_date and :in_end_date </FONT>
<FONT face="Times New Roman" size=3>and ( a.CX_RSN_CODE != 'RVSOFR' AND </FONT>
<FONT size=3><FONT face="Times New Roman"> (a.override_date is null or a.override_date > trunc(a.z_create_date) + 30) </FONT></FONT>
<FONT size=3><FONT face="Times New Roman"> )</FONT></FONT>
<FONT face="Times New Roman" size=3>and a.z_create_user_id in (select assc_id from thrd_pty_comm_assc)</FONT>
<FONT face="Times New Roman" size=3>and (a.z_create_user_id = :in_assoc_id or :in_assoc_id='ALL')</FONT>
<FONT face="Times New Roman" size=3>and d.client_id = b.client_id</FONT>
<FONT face="Times New Roman" size=3>and d.cell_code = b.cell_code</FONT>
<FONT face="Times New Roman" size=3>and (d.cmpgn_code=:in_cmpgn_code or :in_cmpgn_code='ALL')</FONT>
<FONT face="Times New Roman" size=3>order by a.z_create_user_id, b.client_id, b.hs_id,b.hs_sub_seq</FONT>
<FONT size=3><FONT face="Times New Roman"> <o:p></o:p></FONT></FONT>
<FONT face="Times New Roman" size=3>Now, I want to know that can this be done in one single query.</FONT>
-
Posting the same query again won't make it easier for us outsiders to understand it. But you might try posting it in <font size="+2">LARGE UPPERCASE</font> letters, maybe that helps.