Hi, I'm working with Clarity 13.3 and I was wondering if you can help me out. I need to get the query behind this portlet:
I even need something quite more simple than this whole portlet. I need to be able to get the investment name, the allocation and the ETC for an specific set of roles I will add to the query as parameter. I tried using the SQL trace to see If I can get what is running behind this, but that was not good enough.
I tried writing my own simple sql to start but I was able to get data only for the ETC and Allocation in case the role or resource in question is assigned on the effort task and the data I got is only the one for that particular task and not on the other potencial proyect tasks. This is the query I was working with:
select distinct inv.code, inv.name,(team.prallocsum/3600)ALLO, (asig.prestsum/3600)ETC
from niku.inv_investments inv, niku.srm_resources res, niku.prteam team, niku.odf_ca_project ca, niku.prassignment asig
INV.ID = TEAM.PRPROJECTID
and TEAM.PRRESOURCEID = RES.ID
and TEAM.PRRESOURCEID = asig.PRRESOURCEID
and inv.id = ca.id
and asig.PRID = team.prid
and ca.obj_request_category ='category102'
and asig.PRRESOURCEID = '5001***'
I know the issue with that query is the part where I state "and asig.PRID = team.prid" but without that I get duplicated data and weird things.
Any idea or guidance will be highly appreciated.