Clarity

  • 1.  NSQL syntax help to fix a duplicate dimension error

    Posted Sep 08, 2017 02:01 PM

    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?



  • 2.  Re: NSQL syntax help to fix a duplicate dimension error

    Broadcom Employee
    Posted Sep 08, 2017 03:19 PM

    PRTASK table doesn't require, if portlet displays only Project Code, Project Name & Team Resource/Role Name.

     

    You need to include dimenstion ID as team.prid

     

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:pt.prid: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 inv_investments i
    JOIN prteam pt on pt.prprojectid = i.id
    AND @FILTER@

     

     

    Thanks

    Senthil



  • 3.  Re: NSQL syntax help to fix a duplicate dimension error

    Posted Sep 08, 2017 04:20 PM

    Thanks. What I provided is just the relevant part of the query. I do need the prtask table. Regardless, even this query would display the error because of the multiple results.



  • 4.  Re: NSQL syntax help to fix a duplicate dimension error

    Posted Sep 08, 2017 08:12 PM

    I normally do it like this:

     

    select @select:dim:user_def:implied:x:x.unique_id:unique_id@
    , @select:dim_prop:user_def:implied:x:x.project_uuid:project_uuid@
    , @select:dim_prop:user_def:implied:x:x.code:project_id:project_id@
    , @select:dim_prop:user_def:implied:x:x.name:project_name@
    , @select:dim_prop:user_def:implied:x:x.prresourceid:prresourceid@
    , @select:dim_prop:user_def:implied:x:x.task_uuid:task_uuid@
    from (
         select rownum as unique_id, i.id as  project_uuid, i.code as project_id, i.name as project_name, pt.prresourceid as prresourceid, t.id as task_uuid
         from prtask t
         inner join inv_investments i on i.id = t.prprojectid
         left outer join prteam pt on pt.prprojectid = i.id
         where pt.prroleid = '5008004'
    ) x
    where @filter@