3
\$\begingroup\$

As the title say, I have a huge SQL Query which was built over the time (as new requests came, fast workarounds were demanded).More, there were three DB Admins who contributed to this query, and I'm just a poor programmer who has to deal with it.

This query's purpose is to build a report, using some functions and apparently a HUGE SELECT. Now, the problem is that, obviously, it takes 3 hours to complete.

What I'd like is an idea (multiple ideas are welcome) on how could I reduce the run time of this query.

SPOILER ( huge query bellow ):

  CREATE OR REPLACE FORCE VIEW "VDO"."V$RAP_PDM_SI_TRANSITIONS" ("FIRST_TRANS_ID", "TICKET_ID", "TICKET_TYPE", "SERVICE_ID", "New Case at", "FSR(first) at", "FS Done(last) at", "FS Done(last) by", "FS Validation(last) at", "FS validation(last) by", "Contract Definition(first) at", "Contract Signature(last) at", "Config Details(last) at", "Config Details(last) by", "SI(first) at", "SI(first) by", "SI(last) at", "Implementation need by date", "Srv Impl Due Date", "Emergency date", "Project date", "HLD_LL", "WO Generated at", "WO_GEN_FIRST_BY", "WO Closed at", "WO Closed by", "WO pending Stop Clock", "Testing(first) at", "Testing(last) at", "LL_EQP_WO", "START_BILLING_AT", "Start Billing by", "Billing Done at", "Billing Done by", "Closed at", "Cancelled at", "Cancelled by", "Start Porting at", "Porting Req Acc (last) at", "Porting Req Acc (first) at", "START_PORTING_IMP_F_AT", "Porting date", "PORTING_TESTING_L_AT", "First Comment", "First Comment At", "Last Comment", "Comment At", "Test rejected at", "CLIENT_NAME", "IS_CLIENT_WHOLESALER", "FINAL_USER_NAME", "REQUEST_TYPE", "LNO", "SERVICE_TYPE", "SRV_CAPACITY", "COUNTY_A", "LOCALITY_NAME_A", "ADDRESS_A", "COUNTY_Z", "LOCALITY_NAME_Z", "ADDRESS_Z", "CRT_STAT", "PDM_ASSIGNMENT", "BDE_ASSIGNMENT", "DSE_ASSIGNMENT", "EQP_LOC_A", "EQP_LOC_Z", "PACKET_ID", "PACKET_TYPE", "CLIENT_TYPE", "SLA Implementare", "WK_DIF_SI_TESTING", "WK_DIF_SI_PORT_TESTING", "REASON_STOP_CLOCK", "REASON_PENDING_OTHERS", "DURATION_STOP_CLOCK", "DURATION_PENDING_OTHERS", "SLA_IMPL_VALUE", "SLA_DEVIATION", "SLA_STATUS", "IS_INITIATOR_BSR", "CASE_OPENED_BY", "WK_DIF_STOP_CLOCK", "DELIVERY_PENDING_AT_FIRST", "DELIVERY_PENDING_AT_LAST", "DELIVERY_RESUME_DATE_FIRST", "DELIVERY_RESUME_DATE_LAST", "SLA_IMPLEMENTATION_TYPE") AS 
  select first_trans_id, ticket_id, ticket_type, service_id,

infotick.GETDATEFORSTATE(ticket_id, ticket_type,1,'F') "New Case at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,2,'F') "FSR(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'L') "FS Validation(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,16,'L') "FS validation(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'F') "Contract Definition(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,30,'L') "Contract Signature(last) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) by",
infotick.GETDATEFORSTATECORR(ticket_id, ticket_type,18,'L') "SI(last) at",
infotick.getDateValueForFieldState(ticket_id, ticket_type,'IMPL_NEED_BY_DATE', 'S$START_IMPL', 18, 'L') "Implementation need by date",
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'SRV_IMPL_DUE_TIME', 'S$START_IMPL', 18, 'L') "Srv Impl Due Date",
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'EMERGENCY_DATE', 'S$START_IMPL', 18, 'L') "Emergency date",
infotick.getdatecorrvalueforfieldstate(ticket_id, ticket_type, 'PROJECT_DATE', 'S$START_IMPL', 18, 'L') "Project date",
get_ll_eqp(infotick.getStringValueForFieldState(ticket_id, ticket_type, 'LL_EQUIP_ID', 'S$HLD', 167, 'L')) hld_ll,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,17,'F') "WO Generated at",
infotick.getUserForState(ticket_id, ticket_type, 17, 'F') wo_gen_first_by,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed by",
wo_pend_stop_clock "WO pending Stop Clock",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'F') "Testing(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'L') "Testing(last) at",
get_ll_eqp(get_ll_for_case(ticket_id)) ll_eqp_wo,
start_billing_at,
infotick.GETUSERFORSTATE(ticket_id, ticket_type,40,'F') "Start Billing by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,21,'F') "Closed at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,76,'F') "Start Porting at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'L') "Porting Req Acc (last) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'F') "Porting Req Acc (first) at",
start_porting_imp_f_at,
infotick.getDateValueForFieldState(ticket_id, ticket_type,'PORTING_DATE', 'S$START_PORTING_IMPL', 80, 'L') "Porting date",
porting_testing_l_at,
c_first.comments  "First Comment",
c_first.added_at  "First Comment At",
c_last.comments "Last Comment",
c_last.added_at  "Comment At",

infotick.GETDATEFORSTATE(ticket_id, ticket_type,39,'L') "Test rejected at",
client_name, is_client_wholesaler, final_user_name, request_type, lno, service_type, srv_capacity, county_a, 
locality_name_a, address_a,
county_z, locality_name_z, address_z, crt_stat,
getgroupassignment (68, ticket_id, ticket_type) pdm_assignment,
getgroupassignment (41, ticket_id, ticket_type) bde_assignment,
getgroupassignment (43, ticket_id, ticket_type) dse_assignment,
get_ll_eqp(eqp_a) eqp_loc_a,
get_ll_eqp(eqp_z) eqp_loc_z,
packet_id,
packet_type,
get_client_type_desc_for_case(ticket_id) client_type,
decode ((infotick.getStringValueForFieldState(ticket_id, ticket_type, 'SLA_IMPLEMENTARE', 'S$START_IMPL', 18, 'L')),1, 'Standard',2, 'Non-standard', '-') "SLA Implementare",
decode (si_f_at,null,null,decode(testing_f_at,null,null,wkdays(si_f_at,testing_f_at))) wk_dif_si_testing,
decode (start_porting_imp_f_at,null,null,decode(porting_testing_l_at,null,null,wkdays(start_porting_imp_f_at,porting_testing_l_at))) wk_dif_si_port_testing,
get_pending_ext_reason (ticket_id) reason_stop_clock,
get_pending_reason (ticket_id) reason_pending_others,
get_pending_duration (ticket_id, 44) duration_stop_clock,
get_pending_duration (ticket_id, 23) duration_pending_others,

sla.getSLAImpl_new(ticket_id) sla_impl_value,

decode(sla.getSLAImpl_new(ticket_id),null,null,
      decode(si_f_at,null,null,
        decode(testing_f_at,null,null,sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)) )) sla_deviation,


decode(sla.getSLAImpl_new(ticket_id),null,null,
      decode(si_f_at,null,null,
        decode(testing_f_at,null,null,
          decode(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at) - 
                abs(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)),0,'in SLA', 
                decode((infotick.getDateValueForFieldState(ticket_id, ticket_type, 'SRV_IMPL_DUE_TIME', 'S$START_IMPL' , 18, 'L') - trunc(testing_f_at)), 0, 'in SLA','out SLA'))))) sla_status, 

is_user_in_group(opened_by,66) is_initiator_bsr,
case_opened_by,


decode (wo_pend_stop_clock,null,null,decode(due_date,null,null,wkdays(due_date, wo_pend_stop_clock))) wk_dif_stop_clock,

infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'F') delivery_pending_at_first,
infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'L') delivery_pending_at_last,
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'F') delivery_resume_date_first,
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'L') delivery_resume_date_last,

sla_impl.type_name as sla_implementation_type

from (

select trans.first_trans_id,  wk.ticket_id, wk.ticket_type, va.service_id, (select max(asrq_id_loc_a) from s$case_asrq sc where sc.case_id = wk.ticket_id) 

asrqid_loc_a_last ,
(select max(asrq_id_loc_z) from s$case_asrq sc where sc.case_id = wk.ticket_id) asrqid_loc_z_last ,
va.client_name, va.is_client_wholesaler, va.final_user_name, va.request_type,
va.lno, va.service_type, va.capacity||' '||va.capacity_um srv_capacity,
va.county_a, va.locality_name_a, va.address_a,
va.county_z, va.locality_name_z, va.address_z, va.crt_stat,

FSD.LL_EQP_A eqp_a,
FSD.LL_EQP_z eqp_z,

va.packet_id,
va.packet_type,

FSD.CLIENT_APPRV_NEEDED_LOC_A Client_Approval_Needed_a,
FSD.CLIENT_APPRV_NEEDED_LOC_Z Client_Approval_Needed_z,

FSD.ORO_APPRV_NEEDED_LOC_A Oro_Approval_Needed_a,
FSD.ORO_APPRV_NEEDED_LOC_Z Oro_Approval_Needed_z,

infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,18,'F') si_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,38,'F') testing_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,83,'L') porting_testing_l_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,80,'F') start_porting_imp_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type,40,'F') start_billing_at,

va.opened_by opened_by,
va.case_opened_by,


infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type, 44,'L') wo_pend_stop_clock,
infotick.getDateValueForFieldState(wk.ticket_id, wk.ticket_type,'DUE_DATE', 'S$WO_GENERATION', 17, 'L') due_date

from wk$transitions wk, v$all_cases_for_pdm_rap va, s$fs_done fsd, 
(SELECT  MAX (x.trans_id) trid, x.ticket_id tid
FROM wk$transitions x
WHERE x.ticket_type = 'C'
                AND x.to_stat_id = 7
                /*AND x.is_correction IS NULL*/
                AND x.in_pending IS NULL 
                group by x.ticket_id ) trans_id_max_to_fs_done,
 (select min(trans_id) first_trans_id, ticket_id
                from wk$transitions
                where ticket_type = 'C'
                group by ticket_id) trans

where wk.ticket_type='C' and wk.to_stat_id = 1
--and trunc(updated_at) between to_date(:P19_FROM_DATE,'dd-mm-yyyy') and to_date(:P19_TO_DATE,'dd-mm-yyyy')
 and wk.ticket_id = va.case_id

 and trans_id_max_to_fs_done.tid (+)= wk.ticket_id
 AND fsd.trans_id (+) = trans_id_max_to_fs_done.trid
 and trans.ticket_id = wk.ticket_id 

) aux,
    -- comments
    (select case_id, min(id) id
    from wk$comments c
    group by case_id) c_min,
    wk$comments c_first,
    (select case_id, max(id) id
    from wk$comments c
    group by case_id) c_max,
    wk$comments c_last,

-- Lookup table for SLA implementation
lk$implementation_type sla_impl
where ticket_id = c_min.case_id(+)
    and c_min.id = c_first.id(+)    
    and ticket_id = c_max.case_id(+)
    and c_max.id = c_last.id(+)
    -- Join Condition for SLA implementation
    and sla_impl.id (+) = infotick.getStringValueForFieldStateCor(ticket_id, 'C', 'SLA_IMPLEMENTARE','S$START_IMPL',18, 'L');

Some of the function definitions that were used in this query:

create or replace package     infotick as

   function getDateForState(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return date;
   function getDateForStatecorr(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return date;
   function getUserForState(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return varchar2;
   function getUserForStateCorr(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return varchar2;
   function getDateValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return date;
   function getDateCorrValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return date;
   function getDateValueForFieldTrans(p_transid number, p_fieldname varchar2) return date;
   function getStringValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return varchar2;
   function getStringValueForFieldStateCor(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return varchar2;
   function getStringValueForFieldTrans(p_transid number, p_fieldname varchar2) return varchar2;
   function getPortingDateforState(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return date;
   function getNotPortingDateforState(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return date;
   function getWOPortingTransID(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return pls_integer;
   function getWONotPortingTransID(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return pls_integer;
end;

I'll provide any other information if needed.


EDIT: Just to be sure this won't be flagged for not being the maintainer of the code, I'll have to maintain it and add new functionalities to it as new requests come. I've got some experience with SQL but this simply looks like a spaghetti query to me. I'm sure it can be refactored.

\$\endgroup\$
  • \$\begingroup\$ @BCdotWEB that huge SELECT uses SQL Server syntax and as far as I know, the functions are PL/SQL. \$\endgroup\$ – Cajuu' Oct 12 '16 at 9:49
  • \$\begingroup\$ Need to look at the query plan and see what is taking time. I suspect you need to materialize some of the sub queries. \$\endgroup\$ – paparazzo Oct 12 '16 at 10:30
  • \$\begingroup\$ @Paparazzi sure, take your time to analyse the code. There's no hurry. \$\endgroup\$ – Cajuu' Oct 12 '16 at 17:01
  • \$\begingroup\$ Take my time? Need to look at the query plan and see what is taking time. \$\endgroup\$ – paparazzo Oct 12 '16 at 17:07
  • \$\begingroup\$ This is not SQL Server (of course you should know which DBMS you're using), but Oracle code using ancient Outer Join Syntax & unknown functions. It's probably easier to start from scratch. \$\endgroup\$ – dnoeth Oct 12 '16 at 19:25
3
+25
\$\begingroup\$

The first step to cleaning up this beast is to clean up the format of the code that you currently have into something that's readable. I've taken a stab at making it a little easier to read:

select 
    first_trans_id, 
    ticket_id, 
    ticket_type, 
    service_id,
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,1,'F') "New Case at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,2,'F') "FSR(first) at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) by",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'L') "FS Validation(last) at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,16,'L') "FS validation(last) by",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'F') "Contract Definition(first) at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,30,'L') "Contract Signature(last) at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) by",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) by",
    infotick.GETDATEFORSTATECORR(ticket_id, ticket_type,18,'L') "SI(last) at",
    infotick.getDateValueForFieldState(ticket_id, ticket_type,'IMPL_NEED_BY_DATE', 'S$START_IMPL', 18, 'L') "Implementation need by date",
    infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'SRV_IMPL_DUE_TIME', 'S$START_IMPL', 18, 'L') "Srv Impl Due Date",
    infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'EMERGENCY_DATE', 'S$START_IMPL', 18, 'L') "Emergency date",
    infotick.getdatecorrvalueforfieldstate(ticket_id, ticket_type, 'PROJECT_DATE', 'S$START_IMPL', 18, 'L') "Project date",
    get_ll_eqp(infotick.getStringValueForFieldState(ticket_id, ticket_type, 'LL_EQUIP_ID', 'S$HLD', 167, 'L')) hld_ll,
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,17,'F') "WO Generated at",
    infotick.getUserForState(ticket_id, ticket_type, 17, 'F') wo_gen_first_by,
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed by",
    wo_pend_stop_clock "WO pending Stop Clock",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'F') "Testing(first) at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'L') "Testing(last) at",
    get_ll_eqp(get_ll_for_case(ticket_id)) ll_eqp_wo,
    start_billing_at,
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,40,'F') "Start Billing by",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done by",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,21,'F') "Closed at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled at",
    infotick.GETUSERFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled by",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,76,'F') "Start Porting at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'L') "Porting Req Acc (last) at",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'F') "Porting Req Acc (first) at",
    start_porting_imp_f_at,
    infotick.getDateValueForFieldState(ticket_id, ticket_type,'PORTING_DATE', 'S$START_PORTING_IMPL', 80, 'L') "Porting date",
    porting_testing_l_at,
    c_first.comments  "First Comment",
    c_first.added_at  "First Comment At",
    c_last.comments "Last Comment",
    c_last.added_at  "Comment At",
    infotick.GETDATEFORSTATE(ticket_id, ticket_type,39,'L') "Test rejected at",
    client_name, is_client_wholesaler, final_user_name, request_type, lno, service_type, srv_capacity, county_a, 
    locality_name_a, address_a,
    county_z, locality_name_z, address_z, crt_stat,
    getgroupassignment (68, ticket_id, ticket_type) pdm_assignment,
    getgroupassignment (41, ticket_id, ticket_type) bde_assignment,
    getgroupassignment (43, ticket_id, ticket_type) dse_assignment,
    get_ll_eqp(eqp_a) eqp_loc_a,
    get_ll_eqp(eqp_z) eqp_loc_z,
    packet_id,
    packet_type,
    get_client_type_desc_for_case(ticket_id) client_type,
    decode ((infotick.getStringValueForFieldState(ticket_id, ticket_type, 'SLA_IMPLEMENTARE', 'S$START_IMPL', 18, 'L')),1, 'Standard',2, 'Non-standard', '-') "SLA Implementare",
    decode (si_f_at,null,null,decode(testing_f_at,null,null,wkdays(si_f_at,testing_f_at))) wk_dif_si_testing,
    decode (start_porting_imp_f_at,null,null,decode(porting_testing_l_at,null,null,wkdays(start_porting_imp_f_at,porting_testing_l_at))) wk_dif_si_port_testing,
    get_pending_ext_reason (ticket_id) reason_stop_clock,
    get_pending_reason (ticket_id) reason_pending_others,
    get_pending_duration (ticket_id, 44) duration_stop_clock,
    get_pending_duration (ticket_id, 23) duration_pending_others,
    sla.getSLAImpl_new(ticket_id) sla_impl_value,
    decode(sla.getSLAImpl_new(ticket_id),null,null,
    decode(si_f_at,null,null,
    decode(testing_f_at,null,null,sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)) )) sla_deviation,
    decode(sla.getSLAImpl_new(ticket_id),null,null,
    decode(si_f_at,null,null,
    decode(testing_f_at,null,null,
    decode(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at) - 
    abs(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)),0,'in SLA', 
    decode((infotick.getDateValueForFieldState(ticket_id, ticket_type, 'SRV_IMPL_DUE_TIME', 'S$START_IMPL' , 18, 'L') - trunc(testing_f_at)), 0, 'in SLA','out SLA'))))) sla_status, 
    is_user_in_group(opened_by,66) is_initiator_bsr,
    case_opened_by,
    decode (wo_pend_stop_clock,null,null,decode(due_date,null,null,wkdays(due_date, wo_pend_stop_clock))) wk_dif_stop_clock,
    infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'F') delivery_pending_at_first,
    infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'L') delivery_pending_at_last,
    infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'F') delivery_resume_date_first,
    infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'L') delivery_resume_date_last,
    sla_impl.type_name as sla_implementation_type
from 
    (
        select 
            trans.first_trans_id,  
            wk.ticket_id, 
            wk.ticket_type, 
            va.service_id, 
            (
                select 
                    max(asrq_id_loc_a) 
                from s$case_asrq sc 
                where sc.case_id = wk.ticket_id
            ) 
            asrqid_loc_a_last ,
            (
                select 
                    max(asrq_id_loc_z) 
                from s$case_asrq sc 
                where sc.case_id = wk.ticket_id
            ) asrqid_loc_z_last ,
            va.client_name, 
            va.is_client_wholesaler, 
            va.final_user_name, 
            va.request_type,
            va.lno, 
            va.service_type, 
            va.capacity||' '||va.capacity_um srv_capacity,
            va.county_a, 
            va.locality_name_a, 
            va.address_a,
            va.county_z, 
            va.locality_name_z, 
            va.address_z, 
            va.crt_stat,
            FSD.LL_EQP_A eqp_a,
            FSD.LL_EQP_z eqp_z,
            va.packet_id,
            va.packet_type,
            FSD.CLIENT_APPRV_NEEDED_LOC_A Client_Approval_Needed_a,
            FSD.CLIENT_APPRV_NEEDED_LOC_Z Client_Approval_Needed_z,
            FSD.ORO_APPRV_NEEDED_LOC_A Oro_Approval_Needed_a,
            FSD.ORO_APPRV_NEEDED_LOC_Z Oro_Approval_Needed_z,
            infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,18,'F') si_f_at,
            infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,38,'F') testing_f_at,
            infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,83,'L') porting_testing_l_at,
            infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,80,'F') start_porting_imp_f_at,
            infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type,40,'F') start_billing_at,
            va.opened_by opened_by,
            va.case_opened_by,
            infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type, 44,'L') wo_pend_stop_clock,
            infotick.getDateValueForFieldState(wk.ticket_id, wk.ticket_type,'DUE_DATE', 'S$WO_GENERATION', 17, 'L') due_date

        from wk$transitions wk, 
            v$all_cases_for_pdm_rap va, 
            s$fs_done fsd, 
            (
                SELECT  
                    MAX (x.trans_id) trid, 
                    x.ticket_id tid
                FROM wk$transitions x
                WHERE x.ticket_type = 'C'
                    AND x.to_stat_id = 7
                    AND x.in_pending IS NULL 
                group by x.ticket_id 
            ) trans_id_max_to_fs_done,
            (
                select 
                    min(trans_id) first_trans_id, 
                    ticket_id
                from wk$transitions
                where ticket_type = 'C'
                group by ticket_id
            ) trans

        where wk.ticket_type='C' and wk.to_stat_id = 1
            and wk.ticket_id = va.case_id
            and trans_id_max_to_fs_done.tid (+)= wk.ticket_id
            AND fsd.trans_id (+) = trans_id_max_to_fs_done.trid
            and trans.ticket_id = wk.ticket_id 
    ) aux,
    (
        select 
            case_id, 
            min(id) id
        from wk$comments c
        group by case_id
    ) c_min,
    wk$comments c_first,
    (
        select 
            case_id, 
            max(id) id
        from wk$comments c
        group by case_id
    ) c_max,
    wk$comments c_last,
    lk$implementation_type sla_impl
where ticket_id = c_min.case_id(+)
    and c_min.id = c_first.id(+)    
    and ticket_id = c_max.case_id(+)
    and c_max.id = c_last.id(+)
    and sla_impl.id (+) = infotick.getStringValueForFieldStateCor(ticket_id, 'C', 'SLA_IMPLEMENTARE','S$START_IMPL',18, 'L');

If I were maintaining this, the first place I would start would be to clean up the from and where clauses of the main part of the query. Turn all of these into proper inner joins and left outer joins (you will have to work out which ones are appropriate where, start with left outer joins and turn them into inner joins when you want the join to be more strict).

After that, I would dig into the functions to figure out what they're doing, and whether they could just be turned into joins in the from clause.

Another route might be to just start from scratch. Gather all of the business requirements for the report and work from there, rather than supporting this hot mess.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.