AllFolderPaths (Idnr, FolderPath) AS (
      --find all valid Clients on the UC4 system
      select OH_IDnr,
      --T-SQL:
      --CAST(OH_Name as VARCHAR(MAX))
      --Oracle:
      OH_Name
      from OH
      where OH_OTYPE = 'CLNT'
      and OH_Name between '0000' and '9999')
      UNION ALL
      --find all FOLDers on all clients
      select OFS_OH_IDNR_O,
      FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\')
	  from AllFolderPaths
      inner join OFS on Idnr = OFS_OH_Idnr_F
      inner join OH on OFS_OH_Idnr_O = OH_IDNR
      where OH_OTYPE = 'FOLD'
),
-- List all child objects under a folder, given the folder OH_IDnr
child_objects as
(
select OFS_OH_IDNR_O
from OFS
start with OFS_OH_IDNR_F =
  (
    select OH_IDNR from OH where OH_OTYPE='FOLD'
    and OH_Idnr = (
     select Idnr
     from AllFolderPaths
     where FolderPath = 'TESTS|SCOTT'
     )
    and OH_CLIENT=15
  )
connect by prior OFS_OH_IDNR_O = OFS_OH_IDNR_F
),

-- List child object names, IDs, and object types
child_objects_with_names as
(
select OH_name, OH_Idnr, OH_OType from OH,child_objects
where OH.OH_Idnr = child_objects.OFS_OH_IDNR_O
)

-- List all objects under a folder path
select * from child_objects_with_names
where OH_OType <> 'FOLD'