I am trying to get the prt_path for a .fex file in oua_resources in order to join it to the wf_resopobj. Does anyone know of a way this can be done? Or does anyone have a good way to join oua_resources and wf_resopobj together? I haven't been able to find a good way to join these two tables together.

This message has been edited. Last edited by: FP Mod Chuck,

AlexB

I looked at the database diagram under the SQL Server Management console and there is no connection (join) possible between those two tables. What are you trying to accomplish?

You can use a recursive Common Table Expression on the UOA_RESOURCES table to build out the full path of the parent and then join on OBJNAME and PRT_PATH.

Below is a query that we use to see what files are published:

  
--Recursive CTE
WITH RESOURCES_CTE AS (
	SELECT ur.ID
		, ur.NAME                  'OBJNAME'
		, ur.OWNERID
		, ur.PARENTID
		, CAST('' AS VARCHAR(MAX)) 'PRT_PATH'
		, CAST('' AS VARCHAR(MAX)) 'FULL_PATH'
	FROM UOA_RESOURCES ur
	WHERE ur.PARENTID=-1
	UNION ALL
	SELECT ur.ID
		, ur.NAME
		, ur.OWNERID
		, ur.PARENTID
		, CAST(rc.FULL_PATH AS VARCHAR(MAX))
		, CAST(rc.FULL_PATH+'/'+ur.NAME AS VARCHAR(MAX))
	FROM UOA_RESOURCES ur
		INNER JOIN RESOURCES_CTE rc ON ur.PARENTID=rc.ID
	WHERE ur.PARENTID!=-1
)

--Main Query
SELECT wi.DATATYPE              'fileType'
    , wn.OBJDESC                'title'
    , wr.OBJNAME                'fileName'
    , wr.PRT_PATH               'parentPath'
    , CASE u.DESCRIPTION
        WHEN NULL THEN 'Y'
        ELSE 'N'
    END                          'published'
    , ISNULL(u.DESCRIPTION, '')  'owner'
FROM WF_REPOSOBJ wr
    LEFT JOIN WF_ITEM wi ON wr.HANDLE=wi.HANDLE
    LEFT JOIN WF_NLSOBJ wn ON wr.HANDLE=wn.OBJ_HANDLE
    LEFT JOIN RESOURCES_CTE rc ON rc.OBJNAME=wr.OBJNAME
                                AND rc.PRT_PATH=wr.PRT_PATH
    LEFT JOIN UOA_USERS u ON rc.OWNERID=u.ID
WHERE wr.PRT_PATH LIKE '/WFC/Repository/%'
    AND wr.CLASSNAME='WfItem'
ORDER BY wr.PRT_PATH
    , wi.DATATYPE
    , wr.OBJNAME;

This message has been edited. Last edited by: Hallway,

Since the UOA_RESOURCES table is a Hierarchical (Parent-child) table, the recursive CTE works beautifully. This site explains in detail how they work and how to build one: https://www.codeproject.com/Ar...ical-or-parent-child

Thank you for the help everyone! This was very helpful and worked great!

You're welcome. I'm glad that it helped.