Clarity

Expand all | Collapse all

Query to produce a Project Hierarchy

  • 1.  Query to produce a Project Hierarchy

    Posted 04-04-2019 10:00 PM

    Can someone help provide a query to retrieve a Master Project (Name and ID) and all sub projects linked to it (Name and ID).  Have tried some of the queries in the community but have yet to find one working. Appreciate any help out there



  • 2.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 12:53 AM

    The project hierarchy relationship is driven from INV_HIERARCHIES.  For example, below will list projects which are Parents, and their children Projects:

    select parentinv.name parent_prj_name, childinv.name child_prj_name  from inv_hierarchies h
                inner join inv_investments parentinv on h.parent_id = parentinv.id
                inner join inv_investments childinv on h.child_id = childinv.id
                order by 1



  • 3.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 12:58 AM

    I get an error when I put the code into the query: "NPT-0102: NSQL statement must define at least one dimension"



  • 4.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 01:52 AM

    That is because I provided the SQL query. 

     

    There are lots of examples on NSQL which you need to convert the SQL into NSQL.  For example:

    SQL to NSQL 



  • 5.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 02:01 AM

    OK I see.  Would you be able to help provide the full NSQL?.  I am new to this and still learning at an early stage.

    If you can help with the full NSQL, I can see what is happening. Reason I am requesting is that I need this urgently to pull the data.  Thanks



  • 6.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 02:31 AM

    here you go:

    SELECT     
            @SELECT:DIM:USER_DEF:IMPLIED:DT:dt.unique_id:unique_id@,
            @SELECT:DIM_PROP:USER_DEF:IMPLIED:DT:dt.parent_prj_name:parent_prj_name@,
            @SELECT:DIM_PROP:USER_DEF:IMPLIED:DT:dt.parent_prj_code:parent_prj_code@,
            @SELECT:DIM_PROP:USER_DEF:IMPLIED:DT:dt.child_prj_name:child_prj_name@,        
            @SELECT:DIM_PROP:USER_DEF:IMPLIED:DT:dt.child_prj_code:child_prj_code@
    from
    (
    select parentinv.id || '-' || childinv.id unique_id,
    parentinv.name parent_prj_name, parentinv.code parent_prj_code, childinv.name child_prj_name, childinv.code child_prj_code  from inv_hierarchies h
                inner join inv_investments parentinv on h.parent_id = parentinv.id
                inner join inv_investments childinv on h.child_id = childinv.id
    ) dt
    WHERE @FILTER@



  • 7.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 02:56 AM

    Thanks for the NSQL. I did copied it but got another error: "NPT-0117 Structural changes are not allowed for the query that is already in use"



  • 8.  Re: Query to produce a Project Hierarchy

    Posted 04-05-2019 03:07 AM

    Thomas, where the heck are you posting the provided NSQL?  If it is on an existing Query, which is being used on a Portlet, then this would explain that error message.

     

    Create a new query, and copy the NSQL to it - it will work.