Identify Duplicate Project Names

Follow this procedure to identify duplicate project names.

To identify duplicate project names:

1. Open a query editor (for example, SQL Server Management Studio or Oracle SQL Developer) and connect to your database.
2. Run the query to identify all project names that have duplicates.
   For SQL Server

   WITH DUP_DIR AS (
              SELECT DIR_ID, COUNT(DIR_ID) AS DIR_COUNT
   	          FROM WST_DIR
    	         WHERE TABLE_TYPE = 'PROGRAM'
   	          GROUP BY DIR_ID
   	          HAVING COUNT(DIR_ID) > 1
           )
           SELECT D.DIR_ID AS "Dir ID", P.PROGRAM AS "Project ID", D.DIR_UID AS "Dir Uid"
           FROM WST_DIR D
           INNER JOIN DUP_DIR
           ON D.DIR_ID = DUP_DIR.DIR_ID
           LEFT JOIN PROGRAM P
           ON D.DIR_ID COLLATE SQL_Latin1_General_CP1_CS_AS = P.PROGRAM
           WHERE D.TABLE_TYPE = 'PROGRAM'
           ORDER BY D.DIR_ID COLLATE SQL_Latin1_General_CP1_CS_AS DESC

   For Oracle

   WITH DUP_DIR AS (
              SELECT UPPER(DIR_ID) AS DIR_ID, COUNT(1) AS DIR_COUNT
   	          FROM WST_DIR
   	          WHERE TABLE_TYPE = 'PROGRAM'
   	          GROUP BY UPPER(DIR_ID)
   	          HAVING COUNT(1) > 1
           )
           SELECT D.DIR_ID AS "Dir ID", P.PROGRAM AS "Project ID", D.DIR_UID AS "Dir Uid"
           FROM WST_DIR D
           INNER JOIN DUP_DIR
           ON UPPER(D.DIR_ID) = DUP_DIR.DIR_ID
           LEFT JOIN PROGRAM P
           ON D.DIR_ID = P.PROGRAM
           WHERE D.TABLE_TYPE = 'PROGRAM'
           ORDER BY D.DIR_ID

   The query returns 3 columns.

   Column	Description
   Dir ID	This is the ID of the project on the WST_DIR table. These are the values that are causing the duplicate errors.
   Project ID	This is the ID of the project on the PROGRAM table. A NULL value in this column means that the returned row is a duplicate. Normally, there should only be 1 row with a value in this column for a given Dir ID. That row is the valid record and will have the same case for both Dir ID and Project ID. If there is more than 1 row with the same case and value in the Dir ID and Project ID columns, it means there are duplicate records that have the same case. You will need to handle those such that you only have 1 row that has the same case and value in both Dir ID and Project ID.
   Dir Uid	This is the Unique ID for a row on the WST_DIR table. This will be used for identifying which duplicate records to update.