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.


Log in to reply