Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Generating Poor SQL (12.2.1.1 to 12.2.1.4)

Received Response
51
Views
9
Comments
Srinivas Malyala-Oracle
Srinivas Malyala-Oracle Rank 4 - Community Specialist
edited August 13 in Oracle Analytics Cloud and Server

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

Tagged:

Answers

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    First question:

    Is your data source registered correctly in the RPD? Correct database type, correct version?

    And that's OBIA, correct?

  • Srinivas Malyala-Oracle
    Srinivas Malyala-Oracle Rank 4 - Community Specialist

    Hi Christian,

    I have compared post deployed RPD's and I see everything as identical.

    Database Type : Oracle 12c on Exadata 

    Thank you,Srini

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    I wasn't asking if it's identical. I was asking if it is correct. I.e. does it perfectly match your actual database?

    The produced SQL is only as good as the RPD configuration.

  • Srinivas Malyala-Oracle
    Srinivas Malyala-Oracle Rank 4 - Community Specialist

    Yes, it matches.

    SELECT * FROM v$version;

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

  • Gianni Ceresa
    Gianni Ceresa Mod

    Not what you look for but ...

    When an analysis has the whole business logic of the company into a CASE WHEN with 52+ options and it is repeated and string operations added on top of it, I wouldn't blame OBIEE for performance.

    Even just adding a view in that thing would make the analysis 50 times faster (and a materialized view or a table generated by ETL would make it even faster).

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    Ok. There are several things which have changed in 12.2.1.4 of course but it's extremely hard to pinpoint with such a horror of a query...

    So another check first: Are the features of the DB object in the RPD all default values or were some changed?

    pastedImage_0.png

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    For ONCE I didn't immediately point out that the query is atrocious and here you are X-D

  • Srinivas Malyala-Oracle
    Srinivas Malyala-Oracle Rank 4 - Community Specialist

    Hi Christian & Gianni

    Thank you for the suggestions, I will consider whatever I can.

    Prior to the upgrade, this dirty SQL uses to complete in less than 2 mins or so, now taking more than 20mins hence we are killing the SQL on the database.

    I have compared DB "Features" on both versions they match identically. Also, I have looked at consistency check, there are zero errors and 643 warnings, all warnings are identically on both environments. Compared Report  XML on both environments and they are identical. Also compared NSQConfig.INI in both Environments, they are matching.

    Thank you for your inputs.

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    2 minutes?

    All migration issues aside: 2 minutes should be a dead giveaway that the analysis is horrible. Something that's batch mode may be "ok-ish" at 2 minutes, but nobody waits for 2 minutes for results in am interactive dashboard. That's extremely poor design and performance.