Hi,
I have one requirement. I have multiple tables with all tables having columns LAST_UPDATE_DATE in it.
I want to get maximum of LAST_UPDATE_DATE among all those.
I am currently using GREATEST function but it is hampering performance of query.
Any help/ suggestions to replace greatest function would be helpful.
Oracle DB Details
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Sample Query I am Using.I have added some columns of query only.
SELECT
MTLN.TRANSACTION_ID,
MTLN.LAST_UPDATE_DATE,
MTLN.LAST_UPDATED_BY,
LAST_UPDATED_BY.USER_NAME LAST_UPDATED_BY_USER,
MTLN.CREATION_DATE,
MTLN.CREATED_BY,
CREATED_BY.USER_NAME CREATED_BY_USER,
GREATEST( NVL(MTLN.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(MATTR.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(MUTRN.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(LAST_UPDATED_BY.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(CREATED_BY.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(ITEM.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(MPP.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(ITEML.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(HOU.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(TRANSACTION_TYPE.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(TRANSACTION_ACTION.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(ACCTP.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(TASK.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(TTASK.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(STASK.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(TPROJ.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(SPROJ.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')), NVL(PROJ.LAST_UPDATE_DATE,TO_DATE('2001-01-01','YYYY-MM-DD')) ) AS MAX_LAST_UPDATE_DATE
from
FROM MTL_TRANSACTION_LOT_NUMBERS MTLN,
MTL_MATERIAL_TRANSACTIONS MATTR,
MTL_UNIT_TRANSACTIONS MUTRN,
FND_USER LAST_UPDATED_BY,
FND_USER CREATED_BY,
MTL_SYSTEM_ITEMS_B ITEM,
MTL_PARAMETERS MPP,
MTL_SYSTEM_ITEMS_TL ITEML,
HR_ALL_ORGANIZATION_UNITS_TL HOU,
MTL_TRANSACTION_TYPES TRANSACTION_TYPE,
FND_LOOKUP_VALUES_S TRANSACTION_ACTION,
ORG_ACCT_PERIODS ACCTP,
PA_TASKS TASK,
PA_TASKS TTASK,
PA_TASKS STASK,
PA_PROJECTS_ALL TPROJ,
PA_PROJECTS_ALL SPROJ,
PA_PROJECTS_ALL PROJ
WHERE MTLN.TRANSACTION_ID = MATTR.TRANSACTION_ID(+)
AND MATTR.TRANSACTION_ID = MUTRN.TRANSACTION_ID(+)
AND MTLN.LAST_UPDATED_BY = LAST_UPDATED_BY.USER_ID
AND MTLN.CREATED_BY = CREATED_BY.USER_ID(+)
AND MTLN.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND MTLN.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEML.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ITEML.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEML.LANGUAGE = 'US'
AND MTLN.ORGANIZATION_ID = MPP.ORGANIZATION_ID(+)
AND HOU.ORGANIZATION_ID = MTLN.ORGANIZATION_ID
AND HOU.LANGUAGE = 'US'
AND MATTR.TRANSACTION_TYPE_ID = TRANSACTION_TYPE.TRANSACTION_TYPE_ID(+)
AND MATTR.TRANSACTION_ACTION_ID = TRANSACTION_ACTION.LOOKUP_CODE(+)
AND TRANSACTION_ACTION.LOOKUP_TYPE(+) = 'MTL_TRANSACTION_ACTION'
AND TRANSACTION_ACTION.LANGUAGE(+) = 'US'
AND ACCTP.ORGANIZATION_ID(+) = MATTR.ORGANIZATION_ID
AND ACCTP.ACCT_PERIOD_ID(+) = MATTR.ACCT_PERIOD_ID
AND MATTR.TASK_ID = TASK.TASK_ID(+)
AND MATTR.SOURCE_TASK_ID = STASK.TASK_ID(+)
AND MATTR.TO_TASK_ID = TTASK.TASK_ID(+)
AND MATTR.PROJECT_ID = PROJ.PROJECT_ID(+)
AND MATTR.SOURCE_PROJECT_ID = SPROJ.PROJECT_ID(+)
AND MATTR.TO_PROJECT_ID = TPROJ.PROJECT_ID(+)
Any help/ suggestions to replace greatest function would be helpful.
Regards,
Mahesh