Query to produce a Project Hierarchy

[Thomas Tran]

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

[Roland Parrotte]

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

[Thomas Tran]

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

[Roland Parrotte]

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 

[Thomas Tran]

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

[Roland Parrotte]

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@

[Thomas Tran]

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"

[Roland Parrotte]

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.