AnsweredAssumed Answered

NSQL syntax help to fix a duplicate dimension error

Question asked by ClareLeonard76040337 on Sep 8, 2017
Latest reply on Sep 9, 2017 by gcubed

Hi,
I have a 2 dimension portlet that is showing, among other things, the Resource name for Role A on the project. Example:

Project IDProject NameRole A
PRJ-1234System Upgrade Project 1.0Doe, John
PRJ-5678Enhancement Pack 7Smith, Jane
PRJ-0987Policy 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?

Outcomes