SELECT PCMS_CHDOC_DATA.CH_UID, PCMS_CHDOC_DATA.CH_DOC_ID, PCMS_CHDOC_DATA.STATUS,
              PCMS_CHDOC_DATA.TOOL, PCMS_CHDOC_RELATED_ITEMS.FROM_CH_UID,
              PCMS_CHDOC_RELATED_ITEMS.TO_ITEM_UID, PCMS_ITEM_DATA.ITEM_UID,
              PCMS_ITEM_DATA.LIB_FILENAME, PCMS_ITEM_DATA.DESCRIPTION,
              COUNT(PCMS_ITEM_DATA.LIB_FILENAME) OVER (PARTITION BY PCMS_CHDOC_DATA.CH_DOC_ID) COUNT_ID

FROM PCMS_CHDOC_DATA

LEFT JOIN PCMS_CHDOC_RELATED_ITEMS ON
                   PCMS_CHDOC_DATA.CH_UID = PCMS_CHDOC_RELATED_ITEMS.FROM_CH_UID

LEFT JOIN PCMS_ITEM_DATA ON
                   PCMS_CHDOC_RELATED_ITEMS.TO_ITEM_UID = PCMS_ITEM_DATA.ITEM_UID

WHERE PCMS_CHDOC_DATA.STATUS <> 'CLOSED' AND
              PCMS_CHDOC_DATA.TOOL = 'ICD'

GROUP BY PCMS_CHDOC_DATA.CH_UID,
                   PCMS_CHDOC_DATA.CH_DOC_ID,
                   PCMS_CHDOC_DATA.STATUS,
                   PCMS_CHDOC_DATA.TOOL,
                   PCMS_CHDOC_RELATED_ITEMS.FROM_CH_UID,
                   PCMS_CHDOC_RELATED_ITEMS.TO_ITEM_UID,
                   PCMS_ITEM_DATA.ITEM_UID,
                   PCMS_ITEM_DATA.LIB_FILENAME,
                   PCMS_ITEM_DATA.DESCRIPTION

ORDER BY PCMS_CHDOC_DATA.CH_DOC_ID;