AnsweredAssumed Answered

Hierarchiel portlet query assistance

Question asked by emre.emirlioglu on Mar 22, 2016
Latest reply on Apr 15, 2016 by emre.emirlioglu

Hi,

 

I'm developing a hierchial portlet on SQL Server as the one in the print screen below.

 

On depth level 4 OBS test_4 only one row is shown in the portlet even though there should be 2 values, as can be seen in second picture.

 

The NSQL for the portlet can be seen under the print screen.

 

Is it possible to get both values of test_4? Can someone give a hint on what i'm doing wrong?

 

 

 

SELECT  @SELECT:DIM:USER_DEF:IMPLIED:OBS:DATA.id:id@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.name:name@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.depth:depth@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.PARENT_ID:PARENT_ID@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Resource_ID:Resource_ID@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Resource_Name:Resource_Name@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Access_Right_Name:Access_Right_Name@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.granted_through_type:granted_through_type@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.granted_through:granted_through@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Granted_By:Granted_By@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Granted_Date:Granted_Date@

  ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.Access_Right_Type:Access_Right_Type@

        ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBS:DATA.HG_HAS_CHILDREN:hg_has_children@

from

(

SELECT distinct obs.id

,obs.name

,obs.depth

,obs.PARENT_ID

,srm.unique_name "Resource_ID"

,srm.full_name "Resource_Name"

--,sec_groups_v.group_name "Access_Right_Name"

 

 

,STUFF(

  (SELECT distinct ';' + ' ' + sec_groups_v.group_name

  FROM PRJ_OBS_UNITS obs_list

  join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs_list.id

  join srm_resources srm_list on srm_list.user_id = ***_rights.principal_id

  join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

  where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

  AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

  and (obs.id = obs_list.id and srm.user_id = srm_list.user_id) FOR XML PATH('')

  ),1 ,1, ''

)  Access_Right_Name

 

 

--,sec_groups_v.description description

,tidkort.NAME + ':' + obs.NAME granted_through_type

,***_rights.instance_type granted_through

 

 

,(

    SELECT full_name

    FROM   srm_resources

    WHERE  user_id = ***_rights.created_by) "Granted_By"

 

 

,convert(char, (***_rights.created_date), 105) "Granted_Date"

,'Resource Access Rights: OBS Unit' "Access_Right_Type"

 

 

,(case when (select count(*)

  from prj_obs_units obs2

  where obs2.parent_id = obs.id) > 0 

  then obs.id

  else null

  end) hg_has_children

 

 

from PRJ_OBS_UNITS obs

left join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs.id

left join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

left join srm_resources srm on srm.user_id = ***_rights.principal_id

join prj_obs_types tidkort on tidkort.id = obs.type_id --and tidkort.name = 'tidkort'

where obs.depth = '1'

and (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

and @where:param:user_def:string:hg_row_id@ is null

 

 

union

  

SELECT distinct obs.id

,obs.name

,obs.depth

,obs.PARENT_ID

,srm.unique_name "Resource_ID"

,srm.full_name "Resource_Name"

--,sec_groups_v.group_name "Access_Right_Name"

 

 

,STUFF(

  (SELECT distinct ';' + ' ' + sec_groups_v.group_name

  FROM PRJ_OBS_UNITS obs_list

  join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs_list.id

  join srm_resources srm_list on srm_list.user_id = ***_rights.principal_id

  join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

  where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

  AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

  and (obs.id = obs_list.id and srm.user_id = srm_list.user_id) FOR XML PATH('')

  ),1 ,1, ''

)  Access_Right_Name

 

 

--,sec_groups_v.description description

,tidkort.NAME + ':' + obs.NAME granted_through_type

,***_rights.instance_type granted_through

 

 

,(

    SELECT full_name

    FROM   srm_resources

    WHERE  user_id =***_rights.created_by) "Granted_By"

  

,convert(char, (***_rights.created_date), 105) "Granted_Date"

,'Resource Access Rights: OBS Unit' "Access_Right_Type"

 

,(case when (select count(*)

  from prj_obs_units obs2

  where obs2.parent_id = obs.id) > 0 

  then obs.id

  else null

  end) hg_has_children

 

 

from PRJ_OBS_UNITS obs

left join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs.id

left join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

left join srm_resources srm on srm.user_id = ***_rights.principal_id

join prj_obs_types tidkort on tidkort.id = obs.type_id --and tidkort.name = 'tidkort'

where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

and obs.parent_id = @where:param:user_def:string:hg_row_id@ or @where:param:user_def:integer:hg_all_rows@ = 1

 

 

union

  

SELECT distinct obs.id

,obs.name

,obs.depth

,obs.PARENT_ID

,srm.unique_name "Resource_ID"

,srm.full_name "Resource_Name"

--,sec_groups_v.group_name "Access_Right_Name"

 

 

,STUFF(

  (SELECT distinct ';' + ' ' + sec_groups_v.group_name

  FROM PRJ_OBS_UNITS obs_list

  join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs_list.id

  join srm_resources srm_list on srm_list.user_id = ***_rights.principal_id

  join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

  where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

  AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

  and (obs.id = obs_list.id and srm.user_id = srm_list.user_id) FOR XML PATH('')

  ),1 ,1, ''

)  Access_Right_Name

 

 

--,sec_groups_v.description description

,tidkort.NAME + ':' + obs.NAME granted_through_type

,***_rights.instance_type granted_through

 

 

,(

    SELECT full_name

    FROM   srm_resources

    WHERE  user_id =***_rights.created_by) "Granted_By"

  

,convert(char, (***_rights.created_date), 105) "Granted_Date"

,'Resource Access Rights: OBS Unit' "Access_Right_Type"

 

,(case when (select count(*)

  from prj_obs_units obs2

  where obs2.parent_id = obs.id) > 0 

  then obs.id

  else null

  end) hg_has_children

 

 

from PRJ_OBS_UNITS obs

left join cmn_sec_assgnd_right ***_rights on ***_rights.instance_id = obs.id

left join cmn_sec_groups_v sec_groups_v on sec_groups_v.id = ***_rights.right_id

left join srm_resources srm on srm.user_id = ***_rights.principal_id

join prj_obs_types tidkort on tidkort.id = obs.type_id --and tidkort.name = 'tidkort'

where (***_rights.principal_type = 'USER' or ***_rights.principal_type is null)

AND (sec_groups_v.language_code = 'en' or sec_groups_v.language_code is null)

and obs.parent_id = @where:param:user_def:string:hg_row_id@ or @where:param:user_def:integer:hg_all_rows@ = 1

 

 

) data

 

 

where @filter@

Outcomes