Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Published status in WebFOCUS Repository
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Published status in WebFOCUS Repository
 Login/Join
 
Expert
posted Hide Post
I think the only way to work this out is to check the sql in the logs of the DB


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
One of my student workers Tim Ours came up with this code for finding unpublished files:

select t1.*
from sqlpprd.webfocus_repo_pprd.dbo.UOA_RESOURCES t1
where t1.ownerid is not null and
      left(t1.name,1)<>'~' and
      not t1.ownertype = 'I' and
      not (select t2.name
	   from sqlpprd.webfocus_repo_pprd.dbo.UOA_RESOURCES t2
	   where t2.id = t1.parentID) in ('UserInfo', 'IBFS_exportDir')
order by t1.name


If you notice, when looking at the properties for a published file the status will say "Published", but when looking for status for an unpublished file it will say "Private" followed by a username. Because of this, he figured it had a row in one of the user tables and sure enough each had a row in UOA_RESOURCES.

You can get a unique row that can be joined to WF_REPOSOBJ using:

select t1.name as [File Name], t3.name as [Parent Name]
from webfocus_repo.dbo.UOA_RESOURCES t1
inner join sql.webfocus_repo.dbo.UOA_RESOURCES t3 on t3.id = t1.parentid
where t1.ownerid is not null and
      left(t1.name,1)<>'~' and
      not t1.ownertype = 'I' and
      not (select t2.name
	   from sql.webfocus_repo.dbo.UOA_RESOURCES t2
	   where t2.id=t1.parentID) in ('UserInfo', 'IBFS_exportDir')


To get the Parent Name from WF_REPOSOBJ you can use (Someone probably has a nicer way):

select reverse(substring(reverse(PRT_PATH), 0, charindex('/', reverse(PRT_PATH)))) from webfocus_repo.dbo.WF_REPOSOBJ


WebFOCUS 8104
 
Posts: 12 | Registered: June 16, 2015Report This Post
Expert
posted Hide Post
Wow! Thanks Mark. I'll look into this further soon and incorporate it into my code and share.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Ditto here.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Ah, so if there is an entry in the UOA_RESOURCES table, its private.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
This information was very helpful. With these views on both my Dev and QA environments, I've been able to determine new/changed/deleted files and know whether each file is published or not.

First I created a view of the unpublished files:

CREATE VIEW vwPrivateObjects
AS
SELECT t3.NAME as ParentFolder, t1.NAME as ObjectName, PublishedFlag = 'N'
FROM UOA_RESOURCES t1
JOIN UOA_RESOURCES t3 on t3.ID = t1.PARENTID
WHERE t1.OWNERID IS NOT NULL
AND left(t1.NAME,1) <>'~'
AND NOT t1.OWNERTYPE = 'I'
AND NOT (SELECT t2.NAME FROM UOA_RESOURCES t2 WHERE t2.ID=t1.PARENTID) in ('UserInfo', 'IBFS_exportDir')


Then I built a view of all objects. You can add an additional where clause on PRT_PATH to filter to the folders you care about.

CREATE VIEW vwAllObjects
AS
SELECT
Folder = PRT_PATH,
ObjectName = OBJNAME,
ObjectType = SUBSTRING(OBJNAME,CHARINDEX('.',OBJNAME)+1,99),
PublishedFlag = case when p.ObjectName is not null then p.PublishedFlag else 'Y' end,
CreatedUser = CREATEDBY,
CreatedDate = CREATEDON,
ModifiedUser = LASTMODBY,
ModifiedDate = LASTMODON
FROM WF_REPOSOBJ o
LEFT JOIN vwPrivateObjects p on p.ObjectName = o.OBJNAME AND p.ParentFolder = reverse(substring(reverse(o.PRT_PATH), 0, charindex('/', reverse(o.PRT_PATH))))
WHERE CLASSNAME = 'WfItem'


WebFocus 8201
Windows, All Outputs
 
Posts: 1 | Location: United States | Registered: March 31, 2015Report This Post
Expert
posted Hide Post
Thank you Amy for sharing your code!


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Amy, I'm trying to apply your code and I'm having a bit of trouble understanding the LEFT JOIN and reverse - I assume this is not ORACLE SQL...

For Oracle SQL, I have this as the first view:

SELECT 
  T3.NAME AS PARENTFOLDER
, T1.NAME AS OBJECTNAME
, 'N' AS PUBLISHEDFLAG
FROM 
WF_REPOS.UOA_RESOURCES T1
JOIN WF_REPOS.UOA_RESOURCES T3 ON T3.ID = T1.PARENTID
WHERE 
    T1.OWNERID IS NOT NULL
AND T1.NAME NOT LIKE '~%' 
AND NOT T1.OWNERTYPE = 'I' 
AND NOT 
(
    SELECT 
    T2.NAME 
    FROM 
    WF_REPOS.UOA_RESOURCES T2 
    WHERE T2.ID=T1.PARENTID
) IN ('UserInfo', 'IBFS_exportDir')
;


This returns three columns - PARENTFOLDER, OBJECTNAME and PUBLISHEDFLAG.

What happens if the same PARENTFOLDER and OBJECTNAME exist in more than one Domain folder? Will the LEFT JOIN work in your second view?


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
When I place an unpublished fex with the same name in multiple folders - some folders are published, some are not, the following SQL does not return all the unpublished fexes with the matching name:

select 
  T1.name as FILENAME
, t3.name as ParentName
from 
WF_REPOS.UOA_RESOURCES t1
inner join WF_REPOS.UOA_RESOURCES t3 on t3.id = t1.parentid
where 
--T1.OWNERID is not null 
--and
T1.name like 'fm_dummy%' 
--and T1.name not like '~%' 
--and not T1.OWNERTYPE = 'I' 
and not 
(
  select 
  t2.name
  from WF_REPOS.UOA_RESOURCES T2
  where 
  T2.id = T1.PARENTID
) in ('UserInfo', 'IBFS_exportDir')
order by 
t1.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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Still perplexed by this...


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Here's a test scenario.

I have created folders and file as shown here:



The parent folder of an unpublished file must be published for the file to show up in the UOA_RESOURCES folder, therefore, for my test, all folders in yellow are published (I have left all files unpublished).

I have created duplicate folders and files (but placed under different parent folders) to illustrate that the full path needs to be determined to be able to join to the Repository tables.

The following SQL performs a series of joins to create the full path. The number of iterations of joins depends on the maximum level of folders.

This isn't complete: I have to exclude the parent folder name from the full path when it is null or "ROOT".

select 
  T16.name || '/' || T15.name || '/' || T14.name || '/' || T13.name || '/' || T12.name || '/' || T11.name || '/' || T1.name FULL_PATH
, T1.*
, T2.*
, T16.name as t16_name, T15.name as t15_name, T14.name as t14_name, T13.name as t13_name, T12.name as t12_name, T11.name as t11_name
from 
WF_REPOS.UOA_RESOURCES T1
left outer join WF_REPOS.UOA_RESOURCES T11 on T1.PARENTID = T11.id
left outer join WF_REPOS.UOA_RESOURCES T12 on T11.PARENTID = T12.id
left outer join WF_REPOS.UOA_RESOURCES T13 on T12.PARENTID = T13.id
left outer join WF_REPOS.UOA_RESOURCES T14 on T13.PARENTID = T14.id
left outer join WF_REPOS.UOA_RESOURCES T15 on T14.PARENTID = T15.id
left outer join WF_REPOS.UOA_RESOURCES T16 on T15.PARENTID = T16.id
left outer join WF_REPOS.UOA_USERS T2 on T1.OWNERID = T2.id
where 
T1.name like 'fm_dummy100%' 
order by 
T1.name
;

Result:
FULL_PATH                                                                     ID         LINKID  NAME             OWNERID     OWNERTYPE  PARENTID    SHARES  ID_1        CONCURENT  DESCRIPTION       EMAIL              LASTLOGIN                      NAME_1    NAMESPACE  NUMBADTRIES  PASSWORD   PASSWORDSETON                  PRIMGROUPID  PROPERTIES           STATUS  T16_NAME  T15_NAME  T14_NAME    T13_NAME              T12_NAME              T11_NAME
----------------------------------------------------------------------------  ---------- ------  ---------------  ----------  ---------  ----------  ------  ----------  ---------  ----------------  -----------------  -----------------------------  --------  ---------  -----------  ---------  -----------------------------  -----------  -------------------  ------  --------  --------  ----------  --------------------  --------------------  ---------
ROOT/WFC/Repository/_fm_test_folder_main/folder300/fm_folder/fm_dummy100.fex  382494720  (null)  fm_dummy100.fex  1629293192  U          487352323           1629293192  (null)     Mariani, Francis  fmariani@blah.con  2016-11-09 14:53:51.034000000  fmariani  DB         0            crabApple  2014-08-14 12:11:39.281000000  (null)       AuthNType=PreAuthN;  ACTIVE  ROOT      WFC       Repository  _fm_test_folder_main  folder300             fm_folder
/ROOT/WFC/Repository/_fm_test_folder_main/folder300/fm_dummy100.fex           1302986744 (null)  fm_dummy100.fex  1629293192  U          1319665656          1629293192  (null)     Mariani, Francis  fmariani@blah.con  2016-11-09 14:53:51.034000000  fmariani  DB         0            crabApple  2014-08-14 12:11:39.281000000  (null)       AuthNType=PreAuthN;  ACTIVE  (null)    ROOT      WFC         Repository            _fm_test_folder_main  folder300
/ROOT/WFC/Repository/_fm_test_folder_main/folder200/fm_dummy100.fex           664073978  (null)  fm_dummy100.fex  1629293192  U          514127603           1629293192  (null)     Mariani, Francis  fmariani@blah.con  2016-11-09 14:53:51.034000000  fmariani  DB         0            crabApple  2014-08-14 12:11:39.281000000  (null)       AuthNType=PreAuthN;  ACTIVE  (null)    ROOT      WFC         Repository            _fm_test_folder_main  folder200

This message has been edited. Last edited by: Francis Mariani,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Francis,

I apologize upfront if my forum response is a little messy, this is my first post/response...

If your using some native SQL to pull this information from the Repository database, then you can shortcut determining the full path of an object in the UOA_RESOURCES table by using a hierarchical query (also known as recursive query). Below is the SQL I'm using to pull some information I need. It may not directly suit your needs, but you'll get the gist:

SQL SQLORA PREPARE SQLOUT FOR
SELECT A.OBJNAME,
	D.OBJDESC AS TITLE,
	A.OBJTYPE,
	B.DATATYPE,
	A.PRT_PATH,
	DECODE(NVL(TRIM(C.PROPERTY), 'N'), 'N', 'N', 'Y') AS HIDDEN_FLAG,
	DECODE(B.DATATYPE, NULL, NULL, NVL(E.PUBLISHED_FLAG, 'Y')) AS PUBLISHED_FLAG,
	A.LASTACCESSBY,
	A.LASTACCESSON,
	DECODE(A.LASTACCESSON, NULL, NULL, TRUNC(SYSDATE) - TRUNC(A.LASTACCESSON)) AS DAYS_SINCE_LAST_ACC,
	A.LASTMODBY,
	A.LASTMODON,
	A.APPNAME,
	A.CREATEDBY,
	A.CREATEDON,
	A.EXP_DATE,
	A.EXT_ID,
	A.RSNAME,
	A.SRT_ORDER,
	A.HANDLE,
	A.CLASSNAME,
	A.DEF_LNG
FROM REPORTS8.WF_REPOSOBJ A
	LEFT OUTER JOIN REPORTS8.WF_ITEM B
		ON
			A.HANDLE = B.HANDLE
	LEFT OUTER JOIN REPORTS8.WF_OBJPROPS C
		ON
			A.HANDLE = C.OBJ_HANDLE
			AND C.PROPERTY = 'hidden'
	LEFT OUTER JOIN REPORTS8.WF_NLSOBJ D
		ON
			A.HANDLE = D.OBJ_HANDLE
	LEFT OUTER JOIN (
						SELECT A.ID,
							A.LINKID,
							'N' AS PUBLISHED_FLAG,
							A.NAME,
							SYS_CONNECT_BY_PATH(A.NAME, '/') AS HEIR_PATH,
							A.OWNERID,
							A.OWNERTYPE,
							A.PARENTID,
							A.SHARES
						FROM REPORTS8.UOA_RESOURCES A
						WHERE A.ID <> 10401
						START WITH A.PARENTID = 10401
						CONNECT BY NOCYCLE PRIOR A.ID = A.PARENTID
					) E
		ON
			A.PRT_PATH || '/' || A.OBJNAME = E.HEIR_PATH
END


This part of the code contains the heirarchical query (it's nested because I'm too lazy to make a view out of it):


	LEFT OUTER JOIN (
						SELECT A.ID,
							A.LINKID,
							'N' AS PUBLISHED_FLAG,
							A.NAME,
							SYS_CONNECT_BY_PATH(A.NAME, '/') AS HEIR_PATH,
							A.OWNERID,
							A.OWNERTYPE,
							A.PARENTID,
							A.SHARES
						FROM REPORTS8.UOA_RESOURCES A
						WHERE A.ID <> 10401
						START WITH A.PARENTID = 10401
						CONNECT BY NOCYCLE PRIOR A.ID = A.PARENTID
					) E
		ON
			A.PRT_PATH || '/' || A.OBJNAME = E.HEIR_PATH


If you're not familiar with heirarchical queries, you may want to primer up to fully understand what this thing is doing.

However, here are a few items specific to this query:

1. As noted in your post, you had to remove the "ROOT" directory. In this snipit above, the "START WITH A.PARENTID = 10401" line is essentially saying to start with records where the parent record IS the ROOT directory (denoted by the 10401 id). I then filter out the ROOT record just because I'm paranoid like that.

2. In case it's not obvious, the "SYS_CONNECT_BY_PATH(A.NAME, '/') AS HEIR_PATH" row is the row that builds the path and it does include the current record's NAME. So this path includes the object that's in it. Which leads me to item 3.

3. You'll see the last line of this snipit is the join condition. Note that I'm concatenating the WF_REPOSOBJ's path and object columns with a forward slash in between to make the record match the full object path from the UO_RESOURCES sub query. Sheesh that sounds confusing to me and I understand it, I hope you can pick apart that last sentence enough to see what I'm doing.

By using this technique, you don't have to know the "maximum level of folders". It will build the path out for as many levels as you have. If you did want to know how many levels deep a given record is, you could add the native "LEVEL" column into this query and it will tell you.

My next steps are to figure out how to determine if a user's report has been shared with other users. Then I'm going to add a double-nested sub query to build out the path using the WF_NLSOBJ, OBJDESC column to get the "plain english" version of the path.

For anyone reading this who has a SQL Server backend, you can do the same thing using Common Table Expressions. If you're not using plsql or tsql, I can't help you because I'm not that smart.

Hope that helps.


WebFOCUS 8
Windows, All Outputs
 
Posts: 3 | Registered: August 30, 2017Report This Post
Virtuoso
posted Hide Post
Godzilla

Welcome to Focal Point! Thank you so much for making your first post a follow up to a technique for everyone to use. That is awesome!


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Expert
posted Hide Post
Godzilla, thanks for the SQL. I'll have to try it when I'm back in the WF 8 world. That code is quite something!

Chuck, why hasn't anyone from Information Builders helped out with this post? It should have been solved months ago. And why would we have to go through such machinations just to determine if a resource was published or not.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Hi Francis

This was a post from before my time as Focal Point moderator so I can't speak to why no one from IBI helped. I do look at every post now and if I can help I do or if I see that there aren't any solutions I try to enlist the IBI subject matter expert to reply or I suggest opening a case with techsupport. This forum has some very knowledgeable customers like yourself that do a great job of helping but we can't know everything. In looking back at this post something that should be simple does appear to be difficult, the only real way to get product management's attention to issues like this is to open a new feature request case via techsupport. I can help escalate these cases as well.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum Member
posted Hide Post
This is very cool!


webFOCUS 8207.15
WindowsServer 2019
 
Posts: 120 | Location: Minnesota | Registered: August 26, 2013Report This Post
Gold member
posted Hide Post
First of all: Godzilla, totally awesome post. Second- Chuck, any chance that we could get this translated into WebFOCUS?

And if not, how about MSSQL?

Thanks, Steve


WebFOCUS 8
Windows, All Outputs
 
Posts: 71 | Registered: May 29, 2015Report This Post
Member
posted Hide Post
Steve,

If you're using MS SQL Sever, you'll need to use a Common Table Expression (CTE) to perform your recursive query. I have a little less experience with CTEs than with Oracle Connect By so you may have a little trial and error to get the below to work.

ALSO, VERY IMPORTANT...I'm not sure you can run this natively through a WebFOCUS SQL Passthrough procedure. You may have to create a view in your Reports8 database, then use that view in your procedure.

Nextly, keep in mind I don't use a SQL Server backend, so I don't know the column names or table name equivalents, you'll have to port this over to match the actual table and column names (I'm just guessing them).

Lastly before I get going. When you start researching cte's, note that most people do an absolutely abhorrent job at constructing them which is why they can be confusing. Usually (not always), for the posts I see on CTEs, the developers will often alias the CTE with the same name as the table they are using to construct it. I think this is confusing and I would highly discourage people from doing that. Of course, with the amount of times I've seen this done, maybe it is common practice and Microsoft may tell you something different than I do. Regardless, I think it's confusing as **** for newbies. I don't believe there is a standard for column and table aliasing with CTEs. I have been extra careful in choosing my aliases to help explain what the CTE is doing. You can create your own aliases as you see fit.

So the code below emulates just the nested sub query from my oracle code above. However, unlike my nested subquery, a CTE statement goes at the very top of your query.

SQL MSSQL REPARE SQLOUT FOR --not sure what the WebFOCUS Microsoft SQL connector syntax is here, making it up
WITH cteParentUOAResourcePaths(ID, LinkID, PublishedFlag, Name, HeirPath, OwnerID, OwnerType, ParentID, Shares)
--The first half of this union establishes the Seed records for the recursion
	AS (	SELECT SeedRes.ID,
				SeedRes.LinkID,
				'N' AS PublishedFlag,
				SeedRes.Name,
				CAST(SeedRes.Name AS VARCHAR(1024)) AS HeirPath,
				SeedRes.OwnerID,
				SeedRes.OwnerType,
				SeedRes.ParentID,
				SeedRes.Shares
			FROM Reports8.dbo.tUOAResources SeedRes --Again, have no clue what the Schema is in MSSQL, I'm guessing
			WHERE SeedRes.ParentID = 10401 --10401 assumed to be ROOT record ID
				AND SeedRes.ID <> 10401 --10401 assumed to be ROOT record ID
		UNION ALL
--The second half of the union builds out the leaf records
			SELECT LeafRes.ID,
				LeafRes.LinkID,
				'N' AS PublishedFlag,
				LeafRes.Name,
				CAST(cteParentUOAResourcePaths.HeirPath + '/' + CAST(LeafRes.Name AS VARCHAR(1024)) AS VARCHAR(1024)) AS HeirPath,
				LeafRes.OwnerID,
				LeafRes.OwnerType,
				LeafRes.ParentID,
				LeafRes.Shares
			FROM Reports8.dbo.tUOAResources LeafRes
			INNER JOIN cteParentUOAResourcePaths --See how this part of the cte calls itself?  This is the recursive part of this query.
				ON
					LeafRes.ParentID = cteParentUOAResourcePaths.ID
			WHERE LeafRes.ParentID IS NOT NULL)
--Then you start your main query select statement now
SELECT A.ObjName,
	D.ObjDesc,
	A.ObjType
--etc., you fill in the blanks here using my previous post as a guide because I'm too lazy to type it all in...
--When it comes time to join the CTE, it'll look like this:
	LEFT OUTER JOIN cteParentUOAResourcePaths E
		ON
			A.PrtPath + '/' + A.ObjName = E.HeirPath
END


Again, since I'm not using a SQL Server backend, I've obviously not tested this to see if it works. It should get you real close, you'll have to change the column and table names around to match the actual schema.

If you are able to get this to work, please post it up for posterity's sake.

Let me know if you have any questions and I'll do my best to help.

Thanks


WebFOCUS 8
Windows, All Outputs
 
Posts: 3 | Registered: August 30, 2017Report This Post
Expert
posted Hide Post
Godzilla rules!

I am using a MS SQL Server back-end and I wanted to thank you for the CTE code. I'll definitely be trying this out soon and will post my code.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Francis,

No problem. I have drawn from your wisdom in these posts so much that I am only glad to be able to contribute in a meaningful way.

Thanks again!


WebFOCUS 8
Windows, All Outputs
 
Posts: 3 | Registered: August 30, 2017Report This Post
Virtuoso
posted Hide Post
Hi Sh91180

New features are customer driven based on opening a case with techsupport and asking for the feature. Once you have a case open let me know the case number and I can help escalate it to the proper product management folks.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Expert
posted Hide Post
Chuck, thanks for suggesting opening a case.

Meanwhile, perhaps someone from Information Builders could tell us why this is so complicated, or give us the secret, simple answer.

This message has been edited. Last edited by: Francis Mariani,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
I still haven't figured out how to create a report showing the published status for multiple resources in the v8.0.* Repository.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Francis,

Did you ever open a case? ................................lol Wink


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Guru
posted Hide Post
quote:
Originally posted by Francis Mariani:
How can a WebFOCUS "Expert" walk around without a Repository in his back pocket? It's like a nun without her rosary!
Good One


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
Guru
posted Hide Post
It's above my skillset to do this, but I wonder if one could publish or un-publish a fex and then run some kind of audit report from the repository database before-and-after. Maybe that would tell us what gets updated when something gets published.


Webfocus 8
Windows, Linux
 
Posts: 258 | Location: Palm Coast, FL | Registered: February 05, 2010Report This Post
Expert
posted Hide Post
The odd thing is that it might be possible to determine the Published status for one resource, but not for a list. It has to do with the absence or existence of something in a chain of entities: folder > folder > resource. If a folder is published then the resources within the folder can be Published, but if the folder is not published then the resources within cannot be published. You cannot see the Published status for multiple resources anywhere in WebFOCUS: not in App Studio; not in the Resource Tree; not in Change Management - so it seems to me that IBI cannot do it either. OF COURSE, I COULD BE WRONG.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
There is a hierarchical structure to the folders which makes it difficult.

I think this is accurate, but not pretty.

SELECT O.OBJNAME
      ,O.PRT_PATH
      ,ISNULL(P.FEX_OWNER,'published')
FROM WF_REPOSOBJ O
LEFT OUTER JOIN (
	SELECT CAST(ISNULL(R.NAME,' ') AS VARCHAR(64)) AS OBJNAME
		 , CASE
			WHEN CAST(ISNULL(R2.NAME,'') AS VARCHAR(64)) = ' ' THEN ' '
			WHEN CAST(ISNULL(R3.NAME,'') AS VARCHAR(64)) = ' ' THEN '/'
			WHEN CAST(ISNULL(R4.NAME,'') AS VARCHAR(64)) = ' ' THEN '/' + CAST(ISNULL(R2.NAME,'') AS VARCHAR(64))
			WHEN CAST(ISNULL(R5.NAME,'') AS VARCHAR(64)) = ' ' THEN '/' + CAST(ISNULL(R3.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R2.NAME,'') AS VARCHAR(64))
			WHEN CAST(ISNULL(R6.NAME,'') AS VARCHAR(64)) = ' ' THEN '/' + CAST(ISNULL(R4.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R3.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R2.NAME,'') AS VARCHAR(64))
			WHEN CAST(ISNULL(R7.NAME,'') AS VARCHAR(64)) = ' ' THEN '/' + CAST(ISNULL(R5.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R4.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R3.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R2.NAME,'') AS VARCHAR(64))
			WHEN CAST(ISNULL(R8.NAME,'') AS VARCHAR(64)) = ' ' THEN '/' + CAST(ISNULL(R6.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R5.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R4.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R3.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R2.NAME,'') AS VARCHAR(64))
			ELSE '/' + CAST(ISNULL(R7.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R6.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R5.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R4.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R3.NAME,'') AS VARCHAR(64)) + '/' + CAST(ISNULL(R2.NAME,'') AS VARCHAR(64)) END AS PRT_PATH
		  ,CAST(U.DESCRIPTION AS CHAR(100))  AS FEX_OWNER
	  FROM UOA_RESOURCES R
	  LEFT OUTER JOIN UOA_RESOURCES R2
	   ON R2.ID = R.PARENTID
	  LEFT OUTER JOIN UOA_RESOURCES R3
	   ON R3.ID = R2.PARENTID
	  LEFT OUTER JOIN UOA_RESOURCES R4
	   ON R4.ID = R3.PARENTID
	  LEFT OUTER JOIN UOA_RESOURCES R5
	   ON R5.ID = R4.PARENTID
	  LEFT OUTER JOIN UOA_RESOURCES R6
	   ON R6.ID = R5.PARENTID
	  LEFT OUTER JOIN UOA_RESOURCES R7
	   ON R7.ID = R6.PARENTID
	  LEFT OUTER JOIN UOA_RESOURCES R8
	   ON R8.ID = R7.PARENTID
	  INNER JOIN UOA_USERS U
	   ON R.OWNERID = U.ID
	where r.NAME like '%.fex'
	) P
	ON O.OBJNAME = P.OBJNAME
	AND O.PRT_PATH = P.PRT_PATH
WHERE O.OBJNAME like '%.fex'


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Nice work Waz! Thanks for sharing! Big Grin


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Published status in WebFOCUS Repository

Copyright © 1996-2020 Information Builders