Hello All,
We have recently upgraded from 12.2.1.1 to 12.2.1.4 from there onwards OBIEE generating very poor physical SQL for few reports, anyone knows why? Here I am pasting SQLs. Both environments are using the same RPD and the same backend database.
Appreciate your help on this one.
Here is Advance SQL
SET VARIABLE QUERY_SRC_CD='Report';SELECT 0 s_0, "GCS Service Requests"."- Close Date"."Close Month" s_1, "GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" s_2, CASE WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-CANCEL','COMPLETE-CLOSE-EMAIL','COMPLETE-FSTASK-CRU','COMPLETE-FSTASK-FRU')) THEN 'L5 - Fully Automated to Close' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND (("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%')) THEN CASE WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-TRANSFER' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-TRANSFER','ERROR','BUSINESSRULES') THEN 'L3 - Analysis & Recommendation to Engineer or Customer' ELSE 'L5 - Fully Automated to Close' END WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CUSTOMER' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CLOSE-EMAIL' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L4 - Solution Offered to Customer' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" NOT IN ('BUSINESSRULES','COMPLETE-ENRICH','COMPLETE-TRANSFER','ERROR','NO-AUTOMATION')) THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_USED%' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_TDE_DV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_BREAK_GLASS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_EMCLI_ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_NO_ID%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_INT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_MATCH%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_ASIDE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CUS_ACTION%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_CALL%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CR_COLLAB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%WEB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PARSE%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ENRICH%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SRHDR%' THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ROUTE%' AND "GCS Service Requests"."- Poirot"."DX Outcome" NOT LIKE '%L0ROUTE%') THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%') THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_MATCH%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_PARTIAL%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_UNKNOWN%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_NOFILE%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_EXCEPTION%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_ANY%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_FNR%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%DIDNOTHING%' THEN 'none' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'error' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%ERROR%' THEN 'error' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('ERROR','FALLOUT','COMPLETE-TRANSFER','COMPLETE-CUSTOMER','COMPLETE-ENRICH')) THEN 'L0 - Routing Only' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."Service Request"."Automation IT Flag" ='Y' AND "GCS Service Requests"."Service Request"."Automation AT Flag" ='N') THEN 'L0 - Routing Only' ELSE 'other' END s_3, SUBSTRING(CASE WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-CANCEL','COMPLETE-CLOSE-EMAIL','COMPLETE-FSTASK-CRU','COMPLETE-FSTASK-FRU')) THEN 'L5 - Fully Automated to Close' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND (("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%')) THEN CASE WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-TRANSFER' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-TRANSFER','ERROR','BUSINESSRULES') THEN 'L3 - Analysis & Recommendation to Engineer or Customer' ELSE 'L5 - Fully Automated to Close' END WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CUSTOMER' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CLOSE-EMAIL' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L4 - Solution Offered to Customer' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" NOT IN ('BUSINESSRULES','COMPLETE-ENRICH','COMPLETE-TRANSFER','ERROR','NO-AUTOMATION')) THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_USED%' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_TDE_DV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_BREAK_GLASS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_EMCLI_ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_NO_ID%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_INT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_MATCH%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_ASIDE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CUS_ACTION%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_CALL%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CR_COLLAB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%WEB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PARSE%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ENRICH%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SRHDR%' THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ROUTE%' AND "GCS Service Requests"."- Poirot"."DX Outcome" NOT LIKE '%L0ROUTE%') THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%') THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_MATCH%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_PARTIAL%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_UNKNOWN%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_NOFILE%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_EXCEPTION%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_ANY%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_FNR%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%DIDNOTHING%' THEN 'none' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'error' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%ERROR%' THEN 'error' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('ERROR','FALLOUT','COMPLETE-TRANSFER','COMPLETE-CUSTOMER','COMPLETE-ENRICH')) THEN 'L0 - Routing Only' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."Service Request"."Automation IT Flag" ='Y' AND "GCS Service Requests"."Service Request"."Automation AT Flag" ='N') THEN 'L0 - Routing Only' ELSE 'other' END FROM 1 FOR (CASE WHEN LEFT('LL - Must Test on Page',1)='L' THEN 2 ELSE 5 END)) s_4, AGGREGATE(COUNT(DISTINCT "GCS Service Requests"."Service Request"."SR #") BY "GCS Service Requests"."- Close Date"."Close Month") s_5, AGGREGATE(COUNT(DISTINCT "GCS Service Requests"."Service Request"."SR #") BY "GCS Service Requests"."- Close Date"."Close Month", CASE WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-CANCEL','COMPLETE-CLOSE-EMAIL','COMPLETE-FSTASK-CRU','COMPLETE-FSTASK-FRU')) THEN 'L5 - Fully Automated to Close' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND (("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%')) THEN CASE WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-TRANSFER' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-TRANSFER','ERROR','BUSINESSRULES') THEN 'L3 - Analysis & Recommendation to Engineer or Customer' ELSE 'L5 - Fully Automated to Close' END WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CUSTOMER' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CLOSE-EMAIL' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L4 - Solution Offered to Customer' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" NOT IN ('BUSINESSRULES','COMPLETE-ENRICH','COMPLETE-TRANSFER','ERROR','NO-AUTOMATION')) THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_USED%' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_TDE_DV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_BREAK_GLASS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_EMCLI_ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_NO_ID%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_INT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_MATCH%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_ASIDE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CUS_ACTION%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_CALL%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CR_COLLAB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%WEB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PARSE%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ENRICH%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SRHDR%' THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ROUTE%' AND "GCS Service Requests"."- Poirot"."DX Outcome" NOT LIKE '%L0ROUTE%') THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%') THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_MATCH%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_PARTIAL%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_UNKNOWN%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_NOFILE%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_EXCEPTION%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_ANY%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_FNR%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%DIDNOTHING%' THEN 'none' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'error' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%ERROR%' THEN 'error' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('ERROR','FALLOUT','COMPLETE-TRANSFER','COMPLETE-CUSTOMER','COMPLETE-ENRICH')) THEN 'L0 - Routing Only' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."Service Request"."Automation IT Flag" ='Y' AND "GCS Service Requests"."Service Request"."Automation AT Flag" ='N') THEN 'L0 - Routing Only' ELSE 'other' END) s_6, AGGREGATE(COUNT(DISTINCT "GCS Service Requests"."Service Request"."SR #") BY "GCS Service Requests"."- Close Date"."Close Month",SUBSTRING(CASE WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-CANCEL','COMPLETE-CLOSE-EMAIL','COMPLETE-FSTASK-CRU','COMPLETE-FSTASK-FRU')) THEN 'L5 - Fully Automated to Close' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND (("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' OR "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%')) THEN CASE WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-TRANSFER' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('COMPLETE-TRANSFER','ERROR','BUSINESSRULES') THEN 'L3 - Analysis & Recommendation to Engineer or Customer' ELSE 'L5 - Fully Automated to Close' END WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Family" ='Sun_Other' AND "GCS Service Requests"."- Poirot"."End Rule" ='GK_close_sr_terminate') THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_ID%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%FE_REQ%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CUSTOMER' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CLOSE%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%AUTO_CANCEL%' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" ='COMPLETE-CLOSE-EMAIL' THEN 'L5 - Fully Automated to Close' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L4 - Solution Offered to Customer' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" NOT IN ('BUSINESSRULES','COMPLETE-ENRICH','COMPLETE-TRANSFER','ERROR','NO-AUTOMATION')) THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_USED%' THEN 'L4 - Solution Offered to Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_TDE_DV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_BREAK_GLASS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL_EMCLI_ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETSQL%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ATTACHED%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%NO_ENV%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%OPT_OUT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%GETLOG%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%RAPID_AT%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_CUS%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PART_NO_ID%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SOLN_OFFERED_INT%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_MATCH%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_NONE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SIG_ASIDE%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CUS_ACTION%' THEN 'L3 - Analysis & Recommendation to Engineer or Customer' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PRED_DX_CALL%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%CR_COLLAB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%WEB%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%PARSE%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ENRICH%' THEN 'L2 - Log Reader / Parser' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%SRHDR%' THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ROUTE%' AND "GCS Service Requests"."- Poirot"."DX Outcome" NOT LIKE '%L0ROUTE%') THEN 'L2 - Log Reader / Parser' WHEN ("GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' AND "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%') THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_SKIP%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_MATCH%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_PARTIAL%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_UNKNOWN%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_NOFILE%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_EXCEPTION%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_ANY%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%FV_FNR%' THEN 'L1 - Data Collection & File Validation' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%L0ROUTE%' THEN 'L0 - Routing Only' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%DIDNOTHING%' THEN 'none' WHEN "GCS Service Requests"."- Poirot"."DX Outcome" LIKE '%ERROR%' THEN 'error' WHEN "GCS Service Requests"."- Poirot"."FV Outcome" LIKE '%ERROR%' THEN 'error' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."- Automation"."AT Last Flow Status" IN ('ERROR','FALLOUT','COMPLETE-TRANSFER','COMPLETE-CUSTOMER','COMPLETE-ENRICH')) THEN 'L0 - Routing Only' WHEN ("GCS Service Requests"."Product Line Alignment (PLA) Hierarchy"."PLA Line" ='Sun Systems' AND "GCS Service Requests"."Service Request"."Automation IT Flag" ='Y' AND "GCS Service Requests"."Service Request"."Automation AT Flag" ='N') THEN 'L0 - Routing Only' ELSE 'other' END FROM 1 FOR (CASE WHEN LEFT('LL - Must Test on Page',1)='L' THEN 2 ELSE 5 END))) s_7, COUNT(DISTINCT "GCS Service Requests"."Service Request"."SR #") s_8 FROM "GCS Service Requests" WHERE ((("Service Request"."SR Type" = 'Technical') AND ("Service Request"."SR Class" IN ('External', 'Translation')) AND ("Service Request".Substatus <> 'Duplicate') AND ("CSI"."CSI" NOT IN ('15367099', '14731228', '13920780', '15292699', '15292666', '14039837', '17713854', '17807265')) AND ("Service Request"."Group" NOT LIKE 'SMS%')) AND (("Product Line Alignment (PLA) Hierarchy"."PLA Line" = 'EBS')) AND ("- Close Date"."Close Month" BETWEEN '2019 / 04' AND '2019 / 06') AND (("- Poirot"."Event ID" NOT LIKE 'RUM%' AND "- Poirot"."Event ID" NOT LIKE '%RUM') AND ((("Product Line Alignment (PLA) Hierarchy"."PLA Line" <> 'Sun Systems') AND ("- Poirot"."Latest Record by SR Flag" = 'Yes') AND ("- Poirot"."Automation Flag" = 'Y')) OR (("Product Line Alignment (PLA) Hierarchy"."PLA Line" = 'Sun Systems') AND (CONCAT("Service Request"."SR #", EVALUATE('TO_CHAR(%1, %2)' AS CHAR, "- Poirot"."Date Start", 'RRRR-MM-DDHH24:MI:SS') ) = MAX( CONCAT("Service Request"."SR #", EVALUATE('TO_CHAR(%1, %2)' AS CHAR, "- Poirot"."Date Start", 'RRRR-MM-DDHH24:MI:SS') ) )) AND (("- Poirot"."Automation Flag" = 'Y') OR (("Service Request"."Automation IT Flag" = 'Y') AND ("Service Request"."Automation AT Flag" = 'N'))))))) ORDER BY 2 ASC NULLS LAST, 5 DESC NULLS FIRST FETCH FIRST 5000001 ROWS ONLY
Observations from SQLs:
a) 12.2.1.1 got all required fields using a single subquery.
b) 12.2.1.4 has 4 subqueries with the same set of tables
c) 12.2.1.4 not using any advanced function (Partition By and Over)
12.2.1.1 Physical SQL
WITH
SAWITH0 AS (select distinct count(distinct T32495.SR_NUM) over (partition by T33639.PER_NAME_MONTH) as c2,
T33639.PER_NAME_MONTH as c3,
sum(1) over (partition by T33639.PER_NAME_MONTH, T32495.SR_NUM, T495677.PLA_FAMILY_NAME, case when T32495.X_CNSLT_IT_SR_WID = 0 then 'N' else 'Y' end , T632121.LAST_FLOW_STATUS, case when T632121.LAST_FLOW_STATUS in ('AUTOMATION-EVAL', 'NO-AUTOMATION', 'Unspecified') then 'N' else 'Y' end , T691162.AUTOMATION_FLAG, T691162.DX_OUTCOME, T691162.END_RULE, T691162.FV_OUTCOME, T691162.SR_LATEST_REC, TO_CHAR(T691162.DATE_START, 'RRRR-MM-DDHH24:MI:SS')) as c4,
T691162.AUTOMATION_FLAG as c5,
concat(T32495.SR_NUM, TO_CHAR(T691162.DATE_START, 'RRRR-MM-DDHH24:MI:SS')) as c6,
T691162.SR_LATEST_REC as c7,
sum(1) over (partition by T33639.PER_NAME_MONTH, T495677.PLA_LINE_NAME, case when T495677.PLA_LINE_NAME = 'Sun Systems' and T632121.LAST_FLOW_STATUS in ('COMPLETE-CANCEL', 'COMPLETE-CLOSE-EMAIL', 'COMPLETE-FSTASK-CRU', 'COMPLETE-FSTASK-FRU') then 'L5 - Fully Automated to Close' when T495677.PLA_LINE_NAME = 'Sun Systems' and (T495677.PLA_FAMILY_NAME = 'Sun_Other' and T691162.END_RULE = 'GK_close_sr_terminate' or T691162.DX_OUTCOME like '%PART_ID%' or T691162.DX_OUTCOME like '%FE_REQ%' or T691162.DX_OUTCOME like '%AUTO_CLOSE%' or T691162.DX_OUTCOME like '%AUTO_CANCEL%') then case when T632121.LAST_FLOW_STATUS = 'COMPLETE-TRANSFER' and T691162.DX_OUTCOME like '%FE_REQ%' then 'L0 - Routing Only' when T632121.LAST_FLOW_STATUS in ('BUSINESSRULES', 'COMPLETE-TRANSFER', 'ERROR') then 'L3 - Analysis & Recommendation to Engineer or Customer' else 'L5 - Fully Automated to Close' end when T495677.PLA_FAMILY_NAME = 'Sun_Other' and T691162.END_RULE = 'GK_close_sr_terminate' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%AUTO_CLOSE%' and T691162.DX_OUTCOME like '%RAPID_AT%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%PART_ID%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%FE_REQ%' then 'L5 - Fully Automated to Close' when T632121.LAST_FLOW_STATUS = 'COMPLETE-CUSTOMER' and T691162.DX_OUTCOME like '%AUTO_CLOSE%' then 'L4 - Solution Offered to Customer' when T691162.DX_OUTCOME like '%AUTO_CLOSE%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%AUTO_CANCEL%' then 'L5 - Fully Automated to Close' when T632121.LAST_FLOW_STATUS = 'COMPLETE-CLOSE-EMAIL' and T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%RAPID_AT%' and T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L4 - Solution Offered to Customer' when not T632121.LAST_FLOW_STATUS in ('BUSINESSRULES', 'COMPLETE-ENRICH', 'COMPLETE-TRANSFER', 'ERROR', 'NO-AUTOMATION') and T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L4 - Solution Offered to Customer' when T691162.DX_OUTCOME like '%PRED_DX_USED%' then 'L4 - Solution Offered to Customer' when T691162.DX_OUTCOME like '%GETSQL_TDE_DV%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETSQL_BREAK_GLASS%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETSQL_EMCLI_ERROR%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%ATTACHED%' and T691162.DX_OUTCOME like '%GETSQL%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETSQL%' and T691162.DX_OUTCOME like '%NO_ENV%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETSQL%' and T691162.DX_OUTCOME like '%OPT_OUT%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%ERROR%' and T691162.DX_OUTCOME like '%GETSQL%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%ATTACHED%' and T691162.DX_OUTCOME like '%GETLOG%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETLOG%' and T691162.DX_OUTCOME like '%NO_ENV%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETLOG%' and T691162.DX_OUTCOME like '%OPT_OUT%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%ERROR%' and T691162.DX_OUTCOME like '%GETLOG%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETLOG%' and T691162.DX_OUTCOME like '%SIG_NONE%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%RAPID_AT%' and T691162.DX_OUTCOME like '%SIG_NONE%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%PART_NO_ID%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%SOLN_OFFERED_INT%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%SIG_MATCH%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%SIG_NONE%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%SIG_ASIDE%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%CUS_ACTION%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%PRED_DX_CALL%' then 'L2 - Log Reader / Parser' when T691162.DX_OUTCOME like '%CR_COLLAB%' then 'L2 - Log Reader / Parser' when T691162.DX_OUTCOME like '%WEB%' then 'L2 - Log Reader / Parser' when T691162.DX_OUTCOME like '%PARSE%' then 'L2 - Log Reader / Parser' when T691162.DX_OUTCOME like '%ENRICH%' then 'L2 - Log Reader / Parser' when T691162.DX_OUTCOME like '%SRHDR%' then 'L2 - Log Reader / Parser' when not T691162.DX_OUTCOME like '%L0ROUTE%' and T691162.DX_OUTCOME like '%ROUTE%' then 'L2 - Log Reader / Parser' when T691162.DX_OUTCOME like '%L0ROUTE%' and T691162.FV_OUTCOME like '%FV_SKIP%' then 'L0 - Routing Only' when T691162.FV_OUTCOME like '%FV_SKIP%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_MATCH%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_PARTIAL%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_UNKNOWN%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_NOFILE%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_EXCEPTION%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_ANY%' then 'L1 - Data Collection & File Validation' when T691162.FV_OUTCOME like '%FV_FNR%' then 'L1 - Data Collection & File Validation' when T691162.DX_OUTCOME like '%L0ROUTE%' then 'L0 - Routing Only' when T691162.DX_OUTCOME like '%DIDNOTHING%' then 'none' when T691162.DX_OUTCOME like '%ERROR%' then 'error' when T691162.FV_OUTCOME like '%ERROR%' then 'error' when T495677.PLA_LINE_NAME = 'Sun Systems' and T632121.LAST_FLOW_STATUS in ('COMPLETE-CUSTOMER', 'COMPLETE-ENRICH', 'COMPLETE-TRANSFER', 'ERROR', 'FALLOUT') then 'L0 - Routing Only' when case when T632121.LAST_FLOW_STATUS in ('AUTOMATION-EVAL', 'NO-AUTOMATION', 'Unspecified') then 'N' else 'Y' end = 'N' and T495677.PLA_LINE_NAME = 'Sun Systems' and case when T32495.X_CNSLT_IT_SR_WID = 0 then 'N' else 'Y' end = 'Y' then 'L0 - Routing Only' else 'other' end ) as c17,
<
p> count(distinct T32495.SR_NUM) over (partition by T33639.PER_NAME_MONTH, case when T495677.PLA_LINE_NAME = 'Sun Systems' and T632121.LAST_FLOW_STATUS in ('COMPLETE-CANCEL', 'COMPLETE-CLOSE-EMAIL', 'COMPLETE-FSTASK-CRU', 'COMPLETE-FSTASK-FRU') then 'L5 - Fully Automated to Close' when T495677.PLA_LINE_NAME = 'Sun Systems' and (T495677.PLA_FAMILY_NAME = 'Sun_Other' and T691162.END_RULE = 'GK_close_sr_terminate' or T691162.DX_OUTCOME like '%PART_ID%' or T691162.DX_OUTCOME like '%FE_REQ%' or T691162.DX_OUTCOME like '%AUTO_CLOSE%' or T691162.DX_OUTCOME like '%AUTO_CANCEL%') then case when T632121.LAST_FLOW_STATUS = 'COMPLETE-TRANSFER' and T691162.DX_OUTCOME like '%FE_REQ%' then 'L0 - Routing Only' when T632121.LAST_FLOW_STATUS in ('BUSINESSRULES', 'COMPLETE-TRANSFER', 'ERROR') then 'L3 - Analysis & Recommendation to Engineer or Customer' else 'L5 - Fully Automated to Close' end when T495677.PLA_FAMILY_NAME = 'Sun_Other' and T691162.END_RULE = 'GK_close_sr_terminate' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%AUTO_CLOSE%' and T691162.DX_OUTCOME like '%RAPID_AT%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%PART_ID%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%FE_REQ%' then 'L5 - Fully Automated to Close' when T632121.LAST_FLOW_STATUS = 'COMPLETE-CUSTOMER' and T691162.DX_OUTCOME like '%AUTO_CLOSE%' then 'L4 - Solution Offered to Customer' when T691162.DX_OUTCOME like '%AUTO_CLOSE%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%AUTO_CANCEL%' then 'L5 - Fully Automated to Close' when T632121.LAST_FLOW_STATUS = 'COMPLETE-CLOSE-EMAIL' and T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L5 - Fully Automated to Close' when T691162.DX_OUTCOME like '%RAPID_AT%' and T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L4 - Solution Offered to Customer' when not T632121.LAST_FLOW_STATUS in ('BUSINESSRULES', 'COMPLETE-ENRICH', 'COMPLETE-TRANSFER', 'ERROR', 'NO-AUTOMATION') and T691162.DX_OUTCOME like '%SOLN_OFFERED_CUS%' then 'L4 - Solution Offered to Customer' when T691162.DX_OUTCOME like '%PRED_DX_USED%' then 'L4 - Solution Offered to Customer' when T691162.DX_OUTCOME like '%GETSQL_TDE_DV%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_OUTCOME like '%GETSQL_BREAK_GLASS%' then 'L3 - Analysis & Recommendation to Engineer or Customer' when T691162.DX_O
