Need a list of projects without documentation

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

All projects in my Harvest database are designed so that the documentation goes in one repository and all the source code goes in another repository - 2 repositories bound to each project.  We need a report that will show a list of all projects that have no files checked into the documentation repository.

Environment

Release : 13.0.3

Component : CA HARVEST SCM INFRASTRUCTURE (BROKER/AGENT/PEC/SECURITY

Resolution

The query is looking for any repository where “Docu” or “Docs” appears anywhere in the repository name.

SELECT DISTINCT

  HARENVIRONMENT.ENVIRONMENTNAME AS PROJECT,

  HARREPOSITORY.REPOSITNAME AS REPOSITORY,

  NVL(FILECOUNT.NUMBER_OF_FILES, 0) AS NUMBER_OF_FILES

FROM

  HARENVIRONMENT

  INNER JOIN HARVIEW ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID

  INNER JOIN HARREPINVIEW ON HARVIEW.VIEWOBJID = HARREPINVIEW.VIEWOBJID

  INNER JOIN HARREPOSITORY ON HARREPOSITORY.REPOSITOBJID = HARREPINVIEW.REPOSITOBJID

  LEFT JOIN

  (

    SELECT

      HARENVIRONMENT.ENVOBJID,

      HARREPOSITORY.REPOSITOBJID,

      COUNT(DISTINCT HARVERSIONS.VERSIONOBJID) AS NUMBER_OF_FILES

    FROM

      HARENVIRONMENT

      INNER JOIN HARVIEW ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID

      INNER JOIN HARVERSIONINVIEW ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID

      INNER JOIN HARVERSIONS ON HARVERSIONS.VERSIONOBJID = HARVERSIONINVIEW.VERSIONOBJID

      INNER JOIN HARITEMS ON HARITEMS.ITEMOBJID = HARVERSIONS.ITEMOBJID

      INNER JOIN HARREPOSITORY ON HARITEMS.REPOSITOBJID = HARREPOSITORY.REPOSITOBJID

    WHERE

      HARENVIRONMENT.ENVOBJID > 0

      AND HARVERSIONS.ITEMTYPE  = 1

    GROUP BY

      HARENVIRONMENT.ENVOBJID,

      HARREPOSITORY.REPOSITOBJID

  ) FILECOUNT ON HARENVIRONMENT.ENVOBJID = FILECOUNT.ENVOBJID

    AND HARREPOSITORY.REPOSITOBJID = FILECOUNT.REPOSITOBJID

WHERE

  HARENVIRONMENT.ENVOBJID > 0

  AND REGEXP_LIKE (HARREPOSITORY.REPOSITNAME,'(DOCS|DOCU)', 'i')

  AND FILECOUNT.NUMBER_OF_FILES IS NULL

Additional Information

This query was tested with the Oracle DBMS and might need modification to work with SQL Server.