Is there a reason why I get an error running the following select statement.
select
PROGRAMS.PROGRAM_ID,
PROGRAMS.PROGRAM_NAME,
PROGRAMPROJECTS.PROJECT_ID AS MASTERPROJECTID,
PROJECTS.PROJECT_ID AS SUBPROJECTID,
PROJECTS.PROJECT_NAME,
PROJECTS.SCHEDULED_START_DATE,
PROJECTS.SCHEDULED_FINISH_DATE,
PROJECTS.ACTUAL_START_DATE,
PROJECTS.ACTUAL_FINISH_DATE,
PROJECTS.PERCENT_COMPLETE,
BLPROJECTS.SCHEDULED_START_DATE AS BLSTARTDATE,
BLPROJECTS.SCHEDULED_FINISH_DATE AS BLFINISHDATE,
(SYSDATE - BLPROJECTS.SCHEDULED_START_DATE) / (BLPROJECTS.SCHEDULED_FINISH_DATE - BLPROJECTS.SCHEDULED_START_DATE) AS BLPERCENT,
(SYSDATE - PROJECTS.ACTUAL_START_DATE) / (PROJECTS.ACTUAL_FINISH_DATE - PROJECTS.ACTUAL_START_DATE) AS ACTUALPERCENT
from ITG.KPMO_PROGRAMS PROGRAMS
INNER JOIN ITG.KPMO_PROGRAM_PROJECTS PROGRAMPROJECTS
ON PROGRAMS.PROGRAM_ID = PROGRAMPROJECTS.PROGRAM_ID
INNER JOIN ITG.KDRV_PROJECTS PROJECTS
ON PROGRAMPROJECTS.PROJECT_ID = PROJECTS.MASTER_PROJECT_ID
LEFT OUTER JOIN ITG.KDRV_BASELINE_PROJECTS BLPROJECTS
ON PROJECTS.PROJECT_ID = BLPROJECTS.PROJECT_ID
AND CREATION_DATE =
(select MAX(CREATION_DATE) FROM ITG.KDRV_BASELINE_PROJECTS BLPROJECTS2
WHERE PROJECTS.PROJECT_ID = BLPROJECTS2.PROJECT_ID)
WHERE PROGRAMS.PROGRAM_ID = 30040
ORDER BY SUBPROJECTID
I am trying to get the latest baselined rows from a table BY SELECTING THE MAX CREATION DATE FOR THE SAME PROJECTS.
The error is
ORA-01799: a column may not be outer-joined to a subquery
Thanks
select
PROGRAMS.PROGRAM_ID,
PROGRAMS.PROGRAM_NAME,
PROGRAMPROJECTS.PROJECT_ID AS MASTERPROJECTID,
PROJECTS.PROJECT_ID AS SUBPROJECTID,
PROJECTS.PROJECT_NAME,
PROJECTS.SCHEDULED_START_DATE,
PROJECTS.SCHEDULED_FINISH_DATE,
PROJECTS.ACTUAL_START_DATE,
PROJECTS.ACTUAL_FINISH_DATE,
PROJECTS.PERCENT_COMPLETE,
BLPROJECTS.SCHEDULED_START_DATE AS BLSTARTDATE,
BLPROJECTS.SCHEDULED_FINISH_DATE AS BLFINISHDATE,
(SYSDATE - BLPROJECTS.SCHEDULED_START_DATE) / (BLPROJECTS.SCHEDULED_FINISH_DATE - BLPROJECTS.SCHEDULED_START_DATE) AS BLPERCENT,
(SYSDATE - PROJECTS.ACTUAL_START_DATE) / (PROJECTS.ACTUAL_FINISH_DATE - PROJECTS.ACTUAL_START_DATE) AS ACTUALPERCENT
from ITG.KPMO_PROGRAMS PROGRAMS
INNER JOIN ITG.KPMO_PROGRAM_PROJECTS PROGRAMPROJECTS
ON PROGRAMS.PROGRAM_ID = PROGRAMPROJECTS.PROGRAM_ID
INNER JOIN ITG.KDRV_PROJECTS PROJECTS
ON PROGRAMPROJECTS.PROJECT_ID = PROJECTS.MASTER_PROJECT_ID
LEFT OUTER JOIN ITG.KDRV_BASELINE_PROJECTS BLPROJECTS
ON PROJECTS.PROJECT_ID = BLPROJECTS.PROJECT_ID
AND CREATION_DATE =
(select MAX(CREATION_DATE) FROM ITG.KDRV_BASELINE_PROJECTS BLPROJECTS2
WHERE PROJECTS.PROJECT_ID = BLPROJECTS2.PROJECT_ID)
WHERE PROGRAMS.PROGRAM_ID = 30040
ORDER BY SUBPROJECTID
I am trying to get the latest baselined rows from a table BY SELECTING THE MAX CREATION DATE FOR THE SAME PROJECTS.
The error is
ORA-01799: a column may not be outer-joined to a subquery
Thanks