Hi ,
We want to identify a program linked to many master programs . So i created the query and i want to show in hierarchical portlet.
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:hgquery.project_id:project_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.inv_code:inv_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.inv_name:inv_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:sum(hgquery.master_prog):master_prog@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.hg_has_children:hg_has_children@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.last_updated_date:last_updated_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.last_updated_by:last_updated_by@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.last_updated_name:last_updated_name@
from
(
SELECT DISTINCT
i.id project_id,
i.code inv_code,
i.name inv_name,
(select count(*)
FROM
inv_hierarchies, inv_investments inv_p, inv_investments inv_c
WHERE
inv_p.ID = inv_hierarchies.parent_id AND
inv_c.ID = inv_hierarchies.child_id
AND inv_hierarchies.PARENT_id IN (SELECT distinct PRID FROM INV_PROJECTS WHERE IS_PROGRAM = 1)
AND inv_hierarchies.CHILD_ID=I.ID) master_prog,
i.id hg_has_children,
null last_updated_date,
null last_updated_by,
null last_updated_name,
project_type.name project_type
FROM INV_INVESTMENTS i inner join odf_ca_project odf on odf.id=i.id
LEFT OUTER JOIN INV_PROJECTS P ON P.Prid = i.id
where @where:param:user_def:string:hg_row_id@ is null
AND i.is_active = 1
AND (
( i.odf_object_code = 'project'
AND P.Is_Template = 0
AND P.is_Program = 1
)
) GROUP BY i.id, i.code, i.name
union
SELECT
inv_hierarchies.parent_id project_id,INV_P.CODE inv_code,INV_P.NAME inv_name,
0 master_prog,null hg_has_children,
to_char(inv_hierarchies.last_updated_date,'DD/MM/YYYY')last_updated_date,
(select unique_name from srm_Resources a where a.user_id=inv_hierarchies.last_updated_by)last_updated_by,
(select full_name from srm_Resources a where a.user_id=inv_hierarchies.last_updated_by)last_updated_name
FROM
inv_hierarchies left outer join
inv_investments inv_p on inv_p.ID = inv_hierarchies.parent_id
left outer join inv_investments inv_c on inv_c.ID = inv_hierarchies.child_id
left outer join odf_ca_project odf on odf.id=inv_p.id
WHERE
INV_P.IS_ACTIVE=1 AND INV_C.IS_ACTIVE=1
AND inv_hierarchies.PARENT_id IN (SELECT distinct PRID FROM INV_PROJECTS WHERE IS_PROGRAM = 1 and Is_Template = 0
)
and inv_c.ID=@where:param:user_def:string:hg_row_id@
and odf.id=inv_p.id
)hgquery where @filter@ GROUP BY hgquery.project_id, hgquery.inv_code,hgquery.inv_name, hgquery.hg_has_children, hgquery.last_updated_date, hgquery.last_updated_by, hgquery.last_updated_name HAVING( SUM(master_prog)>=1 )
Though I get the expected results when running from oracle. But I am not getting tree structure in clarity? Is there anything wrong ?