Hi,
I have a 2 dimension portlet that is showing, among other things, the Resource name for Role A on the project. Example:
Project ID | Project Name | Role A |
---|
PRJ-1234 | System Upgrade Project 1.0 | Doe, John |
PRJ-5678 | Enhancement Pack 7 | Smith, Jane |
PRJ-0987 | Policy Restructuring | |
In the vast majority of cases, each project has 0 or 1 resources with Role A. However, a few projects have more than 1 person on the project team with this role, which is why the portlet is giving a duplicate dimension error. I'm fine with just showing one person, so I'm trying to add a rownum to that part of the query but I'm having trouble. Here is the relevant part of the query:
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PROJECT:i.id:project_uuid@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:project_id@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.name:project_name@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:pt.prresourceid:controls_resource_id@
FROM prtask t
JOIN inv_investments i on i.id = t.prprojectid
LEFT JOIN prteam pt on pt.prprojectid = i.id and pt.prroleid = '5008004'
I tried changing it to this:
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PROJECT:i.id:project_uuid@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:project_id@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.name:project_name@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:pt.prresourceid:controls_resource_id@
FROM prtask t
JOIN inv_investments i on i.id = t.prprojectid
LEFT JOIN prteam pt on pt.prprojectid = i.id and pt.prroleid =
(SELECT pt.prroleid from prteam pt where pt.prroleid = '5008004' and ROWNUM = 1)
But I get an error on the query, saying:
ORA-01799: a column may not be outer-joined to a subquery
Does anyone have any alternative ideas to accomplish this without the error showing?