SELECT MAX() IN JOIN STATEMENT

[pms18066]

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

 

[SantaMufasa]

PMS,

Without analysing your entire query (for optimisation potentials), the quickest method to resolve your problem is to create a user-defined function to eliminate the subquery:

Section 1 -- User-defined function to replace the subquery:

create or replace function get_top_create_date (Proj_in number) is
    return_date date;
begin
    select MAX(CREATION_DATE) into return_date
      FROM ITG.KDRV_BASELINE_PROJECTS
     WHERE proj_in = PROJECT_ID);
    return return_date;
end;
/

Section 2 -- Modification to your original code:

...
LEFT OUTER JOIN ITG.KDRV_BASELINE_PROJECTS BLPROJECTS 
     ON PROJECTS.PROJECT_ID = BLPROJECTS.PROJECT_ID 
     AND CREATION_DATE = get_top_create_date (PROJECTS.PROJECT_ID)

Try those modifications and let us know if it resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

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 
WHERE     CREATION_DATE = 
                (select MAX(CREATION_DATE) FROM ITG.KDRV_BASELINE_PROJECTS BLPROJECTS2
               WHERE PROJECTS.PROJECT_ID = BLPROJECTS2.PROJECT_ID)

AND PROGRAMS.PROGRAM_ID = 30040
 
                                        
ORDER BY SUBPROJECTID

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]