0

I am new to SQL, and the below view query is taking a long time to return values because of the case statement, can someone help in identifying how I can speed things up?

CREATE OR REPLACE FORCE VIEW "ARBANCDB"."V_GDW_REPLICA" 
          ("WWID", 
           "LAST_NAME", 
           "FIRST_NAME", 
           "MIDDLE_NAME", 
           "FULL_NAME", 
           "SUFFIX_NAME", 
           "CONTINUOUS_SERVICE_DATE", 
           "COST_CENTER_CODE", 
           "COST_CENTER_DESC", 
           "EMPLOYMENT_STATUS", 
           "EMPLOYMENT_STATUS_CODE", 
           "FLSA_CODE", 
           "FTE_PCT", 
           "JOB_FUNCTION_CODE", 
           "JOB_SUBFUNCTION_CODE", 
           "HIRE_DATE", 
           "JOB_FUNCTION", 
           "JOB_GROUP", 
           "JOB_HIERARCHY", 
           "JOB_HIER_CD", 
           "JOB_ID", 
           "JOB_SUBFUNCTION", 
           "LATEST_JOB_EFFECTIVE_DATE", 
           "LEGAL_ENTITY", 
           "LEGAL_ENTITY_CODE", 
           "MANAGER_FIRST_NAME", 
           "MANAGER_FULL_NAME", 
           "MANAGER_LAST_NAME", 
           "MANAGER_POSITION_TITLE", 
           "MANAGER_SUPERVISORY_ORG", 
           "MANAGER_WWID", 
           "MRC", 
           "MRC_CODE", 
           "ORIGINAL_HIRE_DATE", 
           "PAY_FREQUENCY_CODE", 
           "PAY_RATE_TYPE", 
           "POSITION_TITLE", 
           "REPORTING_HIERARCHY", 
           "LEVEL01", 
           "LEVEL02", 
           "LEVEL03", 
           "LEVEL04", 
           "LEVEL05", 
           "LEVEL06", 
           "LEVEL07", 
           "LEVEL08", 
           "LEVEL09", 
           "LEVEL10", 
           "LEVEL11", 
           "LEVEL12", 
           "LEVEL13", 
           "LEVEL14", 
           "LEVEL15", 
           "LEVEL16", 
           "LEVEL17", 
           "LNAME01", 
           "LNAME02", 
           "LNAME03", 
           "LNAME04", 
           "LNAME05", 
           "LNAME06", 
           "LNAME07", 
           "LNAME08", 
           "LNAME09", 
           "LNAME10", 
           "LNAME11", 
           "LNAME12", 
           "LNAME13", 
           "LNAME14", 
           "LNAME15", 
           "LNAME16", 
           "LNAME17", 
           "SALES_INCENTIVE_IND", 
           "SCHEDULED_WEEKLY_HOURS", 
           "SECTOR", 
           "SECTOR_CODE", 
           "SUPERVISORY_ORG", 
           "TERMINATION_DATE", 
           "TIME_TYPE", 
           "WORK_ADDRESS_1", 
           "WORK_ADDRESS_2", 
           "WORK_ADDRESS_3", 
           "WORK_ADDRESS_4", 
           "WORK_CITY", 
           "WORK_COUNTRY", 
           "WORK_COUNTRY_CODE", 
           "WORK_EMAIL", 
           "WORK_POSTAL_CODE", 
           "WORK_REGION", 
           "WORK_STATE_CODE", 
           "WORKER_SUBTYPE", 
           "WORKER_TYPE", 
           "REGION_CODE", 
           "REGION_NAME", 
           "MOBILE_PHONE", 
           "BUSINESS_PHONE", 
           "USER_NAME", 
           "DOMAIN_NAME", 
           "WINDOWS_UPN", 
           "ACQUISITION_CODE", 
           "ACQUISITION_DATE", 
           "DEFAULT_WEEKLY_HOURS", 
           "HR_SOURCE_SYSTEM", 
           "HR_SOURCE_SYSTEM_CODE", 
           "CAMPUS_CODE", 
           "MGR_EMAIL_ADDR", 
           "WORK_STATE_CODE_2", 
           "FIN_CC_CD", 
           "BUS_AREA_CD", 
           "LAST_UPDATE_DATE", 
           "NUMBER_DIRECTREPORTS", 
           "BUSN_UN_CD", 
           "SITE", 
           "JOB_HIER_ID_LVL", 
           "JOB_HIER_NAME_LVL", 
           "ACTV_DIRY_EMAIL_ADDR", 
           "ACTV_DIRY_USRNM", 
           "ROW_ADD_TMS", 
           "ROW_ADD_PGM_NO", 
           "ROW_UPDT_TMS", 
           "ROW_UPDT_PGM_NO") AS 
  SELECT A.wwid, A.last_name, 
         A.first_name, 
         A.middle_name, 
         A.full_name,       
         A.suffix_name, 
         A.continuous_service_date, 
         A.cost_center_code,       
         A.cost_center_desc, 
         A.employment_status,       
         A.employment_status_code, 
         A.flsa_code, A.fte_pct,       
         A.job_function_code, 
         A.job_subfunction_code, 
         A.hire_date,       
         A.job_function, 
         A.job_group, 
         A.job_hierarchy, 
         A.job_hier_cd,       
         A.job_id, 
         A.job_subfunction, 
         A.latest_job_effective_date,       
         A.legal_entity, 
         A.legal_entity_code,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_FIRST_NAME
         ELSE (SELECT B.FIRST_NAME FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS MANAGER_FIRST_NAME,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_FULL_NAME
         ELSE (SELECT B.FULL_NAME FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS MANAGER_FULL_NAME,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_LAST_NAME
         ELSE (SELECT B.LAST_NAME FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS MANAGER_LAST_NAME,    
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.manager_position_title
         ELSE (SELECT B.position_title FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS manager_position_title,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.manager_supervisory_org
         ELSE (SELECT B.supervisory_org FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS manager_supervisory_org,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_WWID
         ELSE HOST_MANAGER_WWID END) AS MANAGER_WWID,
        A.mrc, 
        A.mrc_code, 
        A.original_hire_date,       
        A.pay_frequency_code, 
        A.pay_rate_type, 
        A.position_title,       
        A.reporting_hierarchy, 
        A.level01, 
        A.level02, 
        A.level03,       
        A.level04, 
        A.level05, 
        A.level06, 
        A.level07, 
        A.level08, 
        A.level09,       
        A.level10, 
        A.level11, 
        A.level12, 
        A.level13, 
        A.level14, 
        A.level15,       
        A.level16, 
        A.level17, 
        A.lname01, 
        A.lname02, 
        A.lname03, 
        A.lname04,       
        A.lname05, 
        A.lname06, 
        A.lname07, 
        A.lname08, 
        A.lname09, 
        A.lname10,       
        A.lname11, 
        A.lname12, 
        A.lname13, 
        A.lname14, 
        A.lname15, 
        A.lname16,       
        A.lname17, 
        A.sales_incentive_ind, 
        A.scheduled_weekly_hours,       
        A.sector, 
        A.sector_code, 
        A.supervisory_org, 
        A.termination_date,       
        A.time_type, 
        A.work_address_1, 
        A.work_address_2,       
        A.work_address_3, 
        A.work_address_4, 
        A.work_city, 
        A.work_country,       
        A.work_country_code, 
        (CASE 
         WHEN A.work_email IS NULL THEN A.actv_diry_email_addr 
         ELSE A.work_email END) AS work_email, 
        A.work_postal_code,      
        A.work_region, 
        A.work_state_code, 
        A.worker_subtype,       
        A.worker_type, 
        A.region_code, 
        A.region_name, 
        A.mobile_phone,       
        A.business_phone, 
        A.USER_NAME, 
        A.domain_name, 
        A.windows_upn,       
        A.acquisition_code, 
        A.acquisition_date, 
        A.default_weekly_hours,       
        A.hr_source_system, 
        A.hr_source_system_code, 
        A.campus_code,       
        A.mgr_email_addr, 
        A.work_state_code_2, 
        A.fin_cc_cd,       
        A.bus_area_cd, 
        A.last_update_date, 
        A.number_directreports, 
        (SELECT B.BUSN_UN_CD FROM sapncdb.V_LYNX_COST_CTR_STGEG@sapncdb B WHERE lpad(cost_ctr_cd,10,'0') =A.fin_cc_cd )AS BUSN_UN_CD, 
        (SELECT C.default_site FROM sapncdb.V_LYNX_COST_CTR_STGEG@sapncdb B, arbancdb.BU C WHERE lpad(cost_ctr_cd,10,'0') =A.fin_cc_cd AND B.BUSN_UN_CD = C.bu_cd) AS SITE,
        A.job_hier_id_lvl,
        A.job_hier_name_lvl,
        A.actv_diry_email_addr,
        A.actv_diry_usrnm, 
        A.row_add_tms, 
        A.row_add_pgm_no, 
        A.row_updt_tms,      
        A.row_updt_pgm_no  
   FROM arbancdb.gdw_replica A;
0

Try joining to arbancdb.gdw_replica in your FROM clause (use a LEFT JOIN to avoid filtering. That way you’ll have access to those columns you use in your CASE statements.

|improve this answer|||||
-2

I have attempted to make it a bit faster by using below script:

    CREATE OR REPLACE FORCE VIEW "ARBANCDB"."V_GDW_REPLICA" ("WWID", "LAST_NAME", "FIRST_NAME", "MIDDLE_NAME", "FULL_NAME", "SUFFIX_NAME", "CONTINUOUS_SERVICE_DATE", "COST_CENTER_CODE", "COST_CENTER_DESC", "EMPLOYMENT_STATUS", "EMPLOYMENT_STATUS_CODE", "FLSA_CODE", "FTE_PCT", "JOB_FUNCTION_CODE", "JOB_SUBFUNCTION_CODE", "HIRE_DATE", "JOB_FUNCTION", "JOB_GROUP", "JOB_HIERARCHY", "JOB_HIER_CD", "JOB_ID", "JOB_SUBFUNCTION", "LATEST_JOB_EFFECTIVE_DATE", "LEGAL_ENTITY", "LEGAL_ENTITY_CODE", "MANAGER_FIRST_NAME", "MANAGER_FULL_NAME", "MANAGER_LAST_NAME", "MANAGER_POSITION_TITLE", "MANAGER_SUPERVISORY_ORG", "MANAGER_WWID", "MRC", "MRC_CODE", "ORIGINAL_HIRE_DATE", "PAY_FREQUENCY_CODE", "PAY_RATE_TYPE", "POSITION_TITLE", "REPORTING_HIERARCHY", "LEVEL01", "LEVEL02", "LEVEL03", "LEVEL04", "LEVEL05", "LEVEL06", "LEVEL07", "LEVEL08", "LEVEL09", "LEVEL10", "LEVEL11", "LEVEL12", "LEVEL13", "LEVEL14", "LEVEL15", "LEVEL16", "LEVEL17", "LNAME01", "LNAME02", "LNAME03", "LNAME04", "LNAME05", "LNAME06", "LNAME07", "LNAME08", "LNAME09", "LNAME10", "LNAME11", "LNAME12", "LNAME13", "LNAME14", "LNAME15", "LNAME16", "LNAME17", "SALES_INCENTIVE_IND", "SCHEDULED_WEEKLY_HOURS", "SECTOR", "SECTOR_CODE", "SUPERVISORY_ORG", "TERMINATION_DATE", "TIME_TYPE", "WORK_ADDRESS_1", "WORK_ADDRESS_2", "WORK_ADDRESS_3", "WORK_ADDRESS_4", "WORK_CITY", "WORK_COUNTRY", "WORK_COUNTRY_CODE", "WORK_EMAIL", "WORK_POSTAL_CODE", "WORK_REGION", "WORK_STATE_CODE", "WORKER_SUBTYPE", "WORKER_TYPE", "REGION_CODE", "REGION_NAME", "MOBILE_PHONE", "BUSINESS_PHONE", "USER_NAME", "DOMAIN_NAME", "WINDOWS_UPN", "ACQUISITION_CODE", "ACQUISITION_DATE", "DEFAULT_WEEKLY_HOURS", "HR_SOURCE_SYSTEM", "HR_SOURCE_SYSTEM_CODE", "CAMPUS_CODE", "MGR_EMAIL_ADDR", "WORK_STATE_CODE_2", "FIN_CC_CD", "BUS_AREA_CD", "LAST_UPDATE_DATE", "NUMBER_DIRECTREPORTS", "BUSN_UN_CD", "SITE", "JOB_HIER_ID_LVL", "JOB_HIER_NAME_LVL", "ACTV_DIRY_EMAIL_ADDR", "ACTV_DIRY_USRNM", "ROW_ADD_TMS", "ROW_ADD_PGM_NO", "ROW_UPDT_TMS", "ROW_UPDT_PGM_NO") AS 
      SELECT a.wwid, a.last_name, a.first_name, a.middle_name, a.full_name,       a.suffix_name, a.continuous_service_date, a.cost_center_code,       a.cost_center_desc, a.employment_status,       a.employment_status_code, a.flsa_code, a.fte_pct,       a.job_function_code, a.job_subfunction_code, a.hire_date,       a.job_function, a.job_group, a.job_hierarchy, a.job_hier_cd,       a.job_id, a.job_subfunction, a.latest_job_effective_date,       a.legal_entity, a.legal_entity_code,
      (CASE 
        WHEN    a.IA_TYPE is null 
            THEN a.MANAGER_FIRST_NAME
        ELSE b.FIRST_NAME END) as MANAGER_FIRST_NAME,
      (CASE 
        WHEN    a.IA_TYPE is null   
            THEN a.MANAGER_FULL_NAME
        ELSE b.FULL_NAME END) as MANAGER_FULL_NAME,
     (CASE 
        WHEN    a.IA_TYPE is null 
            THEN a.MANAGER_LAST_NAME
        ELSE b.LAST_NAME END) as MANAGER_LAST_NAME, 
         (CASE 
        WHEN    a.IA_TYPE is null 
            THEN a.manager_position_title
        ELSE b.position_title END) as manager_position_title,
         (CASE 
        WHEN    a.IA_TYPE is null 
            THEN a.manager_supervisory_org
        ELSE b.supervisory_org END) as manager_supervisory_org,
       (CASE 
        WHEN    a.IA_TYPE is null 
            THEN a.MANAGER_WWID
        ELSE a.HOST_MANAGER_WWID END) as MANAGER_WWID,
      a.mrc, a.mrc_code, a.original_hire_date,       a.pay_frequency_code, a.pay_rate_type, a.position_title,       a.reporting_hierarchy, a.level01, a.level02, a.level03,       a.level04, a.level05, a.level06, a.level07, a.level08, a.level09,       a.level10, a.level11, a.level12, a.level13, a.level14, a.level15,       a.level16, a.level17, a.lname01, a.lname02, a.lname03, a.lname04,       a.lname05, a.lname06, a.lname07, a.lname08, a.lname09, a.lname10,       a.lname11, a.lname12, a.lname13, a.lname14, a.lname15, a.lname16,       a.lname17, a.sales_incentive_ind, a.scheduled_weekly_hours,       a.sector, a.sector_code, a.supervisory_org, a.termination_date,       a.time_type, a.work_address_1, a.work_address_2,       a.work_address_3, a.work_address_4, a.work_city, a.work_country,       a.work_country_code, (CASE WHEN a.work_email is null THEN a.actv_diry_email_addr ELSE a.work_email END) as work_email, a.work_postal_code,       a.work_region, a.work_state_code, a.worker_subtype,       a.worker_type, a.region_code, a.region_name, a.mobile_phone,       a.business_phone, a.user_name, a.domain_name, a.windows_upn,       a.acquisition_code, a.acquisition_date, a.default_weekly_hours,       a.hr_source_system, a.hr_source_system_code, a.campus_code,       a.mgr_email_addr, a.work_state_code_2, a.fin_cc_cd,       a.bus_area_cd, a.last_update_date, a.number_directreports, (select b.BUSN_UN_CD from sapncdb.V_LYNX_COST_CTR_STGEG@sapncdb b where lpad(cost_ctr_cd,10,'0') =a.fin_cc_cd )as BUSN_UN_CD, (select c.default_site from sapncdb.V_LYNX_COST_CTR_STGEG@sapncdb b, arbancdb.BU c where lpad(cost_ctr_cd,10,'0') =a.fin_cc_cd and b.BUSN_UN_CD = c.bu_cd) as site,a.job_hier_id_lvl,a.job_hier_name_lvl,a.actv_diry_email_addr,a.actv_diry_usrnm, a.row_add_tms, a.row_add_pgm_no, a.row_updt_tms,       a.row_updt_pgm_no  FROM arbancdb.gdw_replica a left outer join arbancdb.gdw_replica b on a.HOST_MANAGER_WWID=b.wwid;

Hope this helps.

|improve this answer|||||

Your Answer

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

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