You Asked
Tom,
Glad to be able to post a question. Thank you. My questions is How can I improve the efficiency of the following view. This will help not only on this view but similar likes ones throughout the application. Any points will be appreciated. t
Thanks Lou
------------------
SELECT FINANCIAL.FINID,
TO_CHAR (PRIMARY_CLIENT_ID) ID,
PRIMARY_ID_TYPE ID_TYPE,
FINANCIAL.CLIENT_SEGMENT,
(CASE
WHEN FINANCIAL.CLIENT_SEGMENT = 'Individual'
THEN
DECODE (LAST_NAME, NULL, '', LAST_NAME || ', ')
|| FINANCIAL.FIRST_NAME
|| ' '
|| FINANCIAL.MIDDLE_NAME
ELSE
FINANCIAL.LEGAL_NAME
END)
LEGAL_NAME,
FINANCIAL.LAST_NAME,
FINANCIAL.FIRST_NAME,
FINANCIAL.MIDDLE_NAME,
FINANCIAL.CLIENT_TYPE,
FINANCIAL.CLIENT_DOB,
FINANCIAL.DOMICILE_CNTRY_CODE,
DC.CNTRY_NAME DOMICILE_CNTRY_NAME,
FINANCIAL.CITI_SERVICE_CNTRY_CODE,
CC.CNTRY_NAME CITI_SERVICE_CNTRY_NAME,
CC.REGION CITI_SERVICE_REGION,
FINANCIAL.NEXT_PERODIC_REVIEW_DT,
FINANCIAL.RISK_RATING,
FINANCIAL.STATUS,
FINANCIAL.EMPLOYEE_FLAG,
FINANCIAL.EXCLUSION_FLAG,
NVL ( (SELECT 'Y'
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1),
'N')
ACTIVE_WORK_ITEM_IND,
FINANCIAL.COMPONENT,
OS.BRANCH BRANCH,
OS.PRIM_OWNER_SOEID PRIMARY_OWNER,
OS.PRIM_OWNER_BUSINESS_UNIT BUSINESS_UNIT,
(SELECT TASK_ROLE
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1)
ROLE_CODE,
CC.CNTRY_DP_SCENARIO CITI_SERVICE_CNTRY_DP_SCENARIO,
FINANCIAL.LST_RENEW_TRIG_DT,
FINANCIAL.MANAGED_CNTRY_CODE,
FINANCIAL.GFPID,
FINANCIAL.PARENT_CUST_NAME,
FINANCIAL.PARENT_DOMICILECOUNTRYCODE
FROM MAINCLIENT FINANCIAL,
FINAGBL.ISO_COUNTRY_LKUP DC,
FINAGBL.ISO_COUNTRY_LKUP CC,
CORE OS
WHERE FINANCIAL.DOMICILE_CNTRY_CODE = DC.CNTRY_CODE(+)
AND FINANCIAL.PRIMARY_CLIENT_ID IS NOT NULL
AND FINANCIAL.CITI_SERVICE_CNTRY_CODE = CC.CNTRY_CODE(+)
AND OS.FINID(+) = FINANCIAL.FINID
AND OS.COMPONENT(+) = FINANCIAL.COMPONENT
AND FINANCIAL.COMPONENT = 'ACTIVE'
UNION ALL
SELECT FINANCIAL.FINID,
TO_CHAR (PRIMARY_CLIENT_ID) ID,
PRIMARY_ID_TYPE ID_TYPE,
FINANCIAL.CLIENT_SEGMENT,
(CASE
WHEN FINANCIAL.CLIENT_SEGMENT = 'Individual'
THEN
DECODE (LAST_NAME, NULL, '', LAST_NAME || ', ')
|| FINANCIAL.FIRST_NAME
|| ' '
|| FINANCIAL.MIDDLE_NAME
ELSE
FINANCIAL.LEGAL_NAME
END)
LEGAL_NAME,
FINANCIAL.LAST_NAME,
FINANCIAL.FIRST_NAME,
FINANCIAL.MIDDLE_NAME,
FINANCIAL.CLIENT_TYPE,
FINANCIAL.CLIENT_DOB,
FINANCIAL.DOMICILE_CNTRY_CODE,
DC.CNTRY_NAME DOMICILE_CNTRY_NAME,
FINANCIAL.CITI_SERVICE_CNTRY_CODE,
CC.CNTRY_NAME CITI_SERVICE_CNTRY_NAME,
CC.REGION CITI_SERVICE_REGION,
FINANCIAL.NEXT_PERODIC_REVIEW_DT,
FINANCIAL.RISK_RATING,
FINANCIAL.STATUS,
FINANCIAL.EMPLOYEE_FLAG,
FINANCIAL.EXCLUSION_FLAG,
NVL ( (SELECT 'Y'
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1),
'N')
ACTIVE_WORK_ITEM_IND,
FINANCIAL.COMPONENT,
OS.BRANCH BRANCH,
OS.PRIM_OWNER_SOEID PRIMARY_OWNER,
OS.PRIM_OWNER_BUSINESS_UNIT BUSINESS_UNIT,
(SELECT TASK_ROLE
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1)
ROLE_CODE,
CC.CNTRY_DP_SCENARIO CITI_SERVICE_CNTRY_DP_SCENARIO,
FINANCIAL.LST_RENEW_TRIG_DT,
FINANCIAL.MANAGED_CNTRY_CODE,
FINANCIAL.GFPID,
FINANCIAL.PARENT_CUST_NAME,
FINANCIAL.PARENT_DOMICILECOUNTRYCODE
FROM MAINCLIENT FINANCIAL,
FINAGBL.ISO_COUNTRY_LKUP DC,
FINAGBL.ISO_COUNTRY_LKUP CC,
CORE OS
WHERE FINANCIAL.DOMICILE_CNTRY_CODE = DC.CNTRY_CODE(+)
AND FINANCIAL.PRIMARY_CLIENT_ID IS NOT NULL
AND FINANCIAL.CITI_SERVICE_CNTRY_CODE = CC.CNTRY_CODE(+)
AND OS.FINID(+) = FINANCIAL.FINID
AND OS.COMPONENT(+) = FINANCIAL.COMPONENT
AND FINANCIAL.COMPONENT = 'RUNNING'
AND NOT EXISTS
(SELECT 1
FROM MAINCLIENT ACT
WHERE ACT.FINID = FINANCIAL.FINID
AND ACT.COMPONENT = 'ACTIVE');
and Tom said...
Is this answer out of date? If it is, please let us know via a Comment