AnsweredAssumed Answered

Query Behind a Portlet

Question asked by juan.segovia on Jul 23, 2014
Latest reply on Jul 25, 2014 by urmas

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:

 

Screen.jpg

 

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

where

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.

 

Regards,

 

Juan

Outcomes