JOIN GROUPID IN WF_REPOS_UOA_GROUPSUSERS TO ID IN WF_REPOS_UOA_GROUPS AS J1
JOIN USERID  IN WF_REPOS_UOA_GROUPSUSERS TO ID IN WF_REPOS_UOA_USERS  AS J2

TABLE FILE WF_REPOS_UOA_GROUPSUSERS
PRINT 
WF_REPOS_UOA_GROUPS.DESCRIPTION AS 'Group Description'
WF_REPOS_UOA_GROUPS.NAME        AS 'Group Name'

WF_REPOS_UOA_USERS.DESCRIPTION  AS 'User Description'
WF_REPOS_UOA_USERS.NAME         AS 'User Name'

BY WF_REPOS_UOA_USERS.DESCRIPTION NOPRINT

WHERE WF_REPOS_UOA_GROUPS.NAME EQ 'Treasury_Power_Users'
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 select x.id
      ,x.parentid
      ,x.name
      ,t2.groupid
      ,t2.userid
      ,t3.name
from (select t1.id
	    ,t1.name
	from uoa_groups t1
	start with t1.id in (select groupid
				from uoa_groupsusers gu
				group by groupid) x
	,uoa_groupsusers t2
	,uoa_users t3
where x.id = t2.groupid
  and t2.userid = t3.id
order by t3.name, x.id, x.name
group by t3.name, x.id, x.name
 

quote:

select x.id
      ,x.parentid
      ,x.name
      ,t2.groupid
      ,t2.userid
      ,t3.name
from (select t1.id
	    ,t1.name
	from uoa_groups t1
	start with t1.id in (select groupid
				from uoa_groupsusers gu
				group by groupid) x
	,uoa_groupsusers t2
	,uoa_users t3
where x.id = t2.groupid
  and t2.userid = t3.id
order by t3.name, x.id, x.name
group by t3.name, x.id, x.name

(FOC1400) SQLCODE IS 1788 (HEX: 000006FC)
 : ORA-01788: CONNECT BY clause required in this query block
 L    (FOC1405) SQL PREPARE ERROR.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

SQL SQLORA
select 
	  t3.name
	  ,t3.description
	  ,x.*
from (select id, name as "Group_Name", connect_by_root name as "Top_Level_Group", sys_connect_by_path(name,'/') as "Path"
	from uoa_groups
	start with parentid = -1
	connect by parentid = prior id
	order siblings by parentid) x
	,uoa_groupsusers t2
	,uoa_users t3
where x.id = t2.groupid
  and t2.userid = t3.id;
order by x.path, t3.description
TABLE FILE SQLOUT
HEADING
"User/group Recursive query"
PRINT *
ON TABLE SET LINES 99999
END  

SELECT 
  X.ID
, X.PARENTID
, X.NAME
, T2.GROUPID
, T2.USERID
, T3.NAME
FROM 
(
    SELECT 
      T1.ID
    , T1.NAME
    FROM 
    ENTRPT.UOA_GROUPS T1
    START WITH 
        parent is null
    connect by
        prior child=parent 
    and T1.ID IN 
    (
        SELECT 
        GROUPID
        FROM 
        ENTRPT.UOA_GROUPSUSERS GU
        GROUP BY 
        GROUPID
    ) X
    , ENTRPT.UOA_GROUPSUSERS T2
    , ENTRPT.UOA_USERS T3
    WHERE 
         X.ID = T2.GROUPID
    AND T2.USERID = T3.ID
    ORDER BY T3.NAME, X.ID, X.NAME
    GROUP BY T3.NAME, X.ID, X.NAME
)
;

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

select 
  x.id
, parentid
, x.name "GroupName"
, t2.groupid
, t2.userid
, t3.DESCRIPTION "USER_NAME"
, t3.name "UserName"
, SYS_CONNECT_BY_PATH(x.name, '/') "Path"
, LEVEL
from 
  WF_REPOS.uoa_groups x
, WF_REPOS.uoa_groupsusers t2
, WF_REPOS.uoa_users t3
where 
    x.id = t2.groupid 
AND t2.userid = t3.ID 
START WITH x.name <> 'blah'
CONNECT BY PRIOR x.ID = x.parentid
ORDER BY 
  x.NAME
, t3.name
;

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

http://host:port/ibi_apps/rs/ibfs/SSYS/USERS?IBIRS_action=get

Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster

quote:

WF returns the results in XML format. We save the XML into a file, and then open in MS Excel to obtain our report.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Select x.Path, x.Pathlen, x.ID AS Group_ID, x.CONNECT_BY_ROOTID, t1.name AS Group_Name, x.name AS MemeberOf, t2.userid
      ,t3.name AS User_Name
from (
select ID, connect_by_root ID, LEVEL-1 AS Pathlen, SYS_CONNECT_BY_PATH(ID, '/') AS Path, name 
from uoa_groups 
--where level > 0 
connect by prior parentid = id) x
,uoa_groups t1
,uoa_groupsusers t2
,uoa_users t3
where t1.id = x.connect_by_rootid
    and T2.GROUPID = x.ID
    and t2.USERID = t3.ID
group by x.Path, x.Pathlen, x.ID, x.CONNECT_BY_ROOTID, t1.name,x.name, t2.userid, t3.name
Order by x.Path, x.Pathlen, x.ID, x.CONNECT_BY_ROOTID, t1.name, x.name, t2.userid, t3.name; 

quote:

So one cannot use these WebFOCUS RESTful Web Services to create WebFOCUS reports?