I am facing a issue when building a hierarchical knowledge store custom portlet that allow us to filter on the name of the folder or files that are in folder.
When I filter on folder I got the folder without their descendants ( see screenshot below)
And when I filter on a file name I got no results.
That the query used to build that portlet.
SELECT CF.ID ID,
WHEN ( SELECT COUNT(*)
FROM CLB_DMS_FOLDERS CF1,
WHERE CFI1.PARENT_FOLDER_ID = CF.ID ) = 0 THEN NULL
FROM CLB_DMS_FOLDERS CF
WHERE CF.FOLDER_TYPE = 'StandardFolder'
AND CF.PATH_NAME LIKE '/Root/DMS/KS/INFRA'
AND @where:param:user_def:string:hg_row_id@ is null
from CLB_DMS_FILES CFI
WHERE CFI.PARENT_FOLDER_ID= @where:param:user_def:string:hg_row_id@) KStore
If someone can help to get this working.