Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECT MAX() IN JOIN STATEMENT

Status
Not open for further replies.

pms18066

Programmer
Joined
Jan 12, 2005
Messages
94
Location
US
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
 
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:
Code:
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:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ 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.
 
Code:
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

[bandito] [blue]DBomrrsm[/blue] [bandito]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top