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;