Jim Griffith

Pulling folder path for a specific object

Discussion created by Jim Griffith on Aug 16, 2017
Latest reply on Aug 17, 2017 by Wolfgang_Brueckler_1288
I've searched for a good way to do this and wasn't happy with what I was finding. So, I created my own way to do it and wanted to share. My goal was to have a job create a vara in the folder the object lived in. I also wanted to have the ability to use this in multiple places without having to modify the create_object code for the folder the job was in. I put the SQL below into a sec_sqli vara and passed in the job name and the client number. The format returned is "fold1/fold2/fold3...etc". I was able to then use this value in my create_object script to create my vara object. 

declare @tmp varchar(250)
SET @tmp = '';

WITH FOLDERPATH (IDNR, FPATH, OLEVEL)
AS
(
    SELECT OH.OH_IDNR
                      ,  RIGHT(OH.OH_NAME, (LEN(OH.OH_NAME) - CHARINDEX('\', OH.OH_NAME)))
                           ,  OFS.OFS_Level AS OLEVEL
    FROM OFS AS OFS
         INNER JOIN OH AS OH
                  ON OH.OH_IDNR = OFS.OFS_OH_IDNR_O
    WHERE OFS.OFS_OH_IDNR_O = (SELECT OFS.OFS_OH_IDNR_F
                                       FROM OFS AS OFS
                                       INNER JOIN OH AS OH
                                               ON OH.OH_IDNR = OFS.OFS_OH_IDNR_O
                                       WHERE OH.OH_NAME = <object name>
                                                  AND OH.OH_CLIENT = <client number>)

    UNION ALL

    SELECT OH.OH_IDNR
                           ,  RIGHT(OH.OH_NAME, (LEN(OH.OH_NAME) - CHARINDEX('\', OH.OH_NAME)))
                          ,  OLEVEL - 1
    FROM OFS AS OFS
         INNER JOIN OH AS OH
                  ON OH.OH_IDNR = OFS.OFS_OH_IDNR_F
    INNER JOIN FOLDERPATH AS FP
                 ON FP.IDNR = OFS.OFS_OH_IDNR_O
        WHERE OH.OH_OTYPE = 'FOLD'
)

SELECT @TMP = @TMP + FPATH + '/' 
FROM FOLDERPATH 
ORDER BY OLEVEL ASC

SELECT SUBSTRING(@TMP, 1, (LEN(@TMP) - 1))
GO


Hopefully this helps anyone looking to do something similar. 

Outcomes