Cobra Help

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.

Parent Topic:

Fixing Duplicate Project Names