Background

Ticket #6901 was created to partially handle the timeouts encountered during regression testing. The ticket noted that many duplicate queries occur against camdecmpsmd.VW_DEFAULTS_MASTER_DATA_RELATIONSHIPS for data that will not change. The view itself complexly combines data related to Defaults into columns in a way that produces duplicate values for any particular column. DTOs use this view and other similar views to grab a set of individual values for validations. Essentially the view pulls together a complicated set of information, but DTOs pull one piece out of that information and has to apply a distinct to eliminate duplicates.

The solution in #6901 was to replace the affected views with the actual underlying lookup table in many cases, and with simpler views in other cases. However it was noted during a PR review that Distinct was used on selects with the replacement views that should not contain duplicates. In these cases the views were based on the CROSS_CHECK_CATALOG_VALUE table for rows with a specific CROSS_CHK_CATALOG_ID. Although a unique key constraint does not exist to this affect, duplicate rows with the same CROSS_CHK_CATALOG_ID, VALUE1, VALUE2, and VALUE3 values should not exist.

Currently Existing Duplicates

Formula Parameter and Component Type and Basis to Formula Code (16) Crosscheck

Parameter Code to Fuel Code for Defaults (193) Crosscheck

Duplicate Crosscheck Query

select  ccc.cross_chk_catalog_id,
        ccc.cross_chk_catalog_name,
        ccv.value1,
        ccv.value2,
        ccv.value3,
        string_agg( ccv.cross_chk_catalog_value_id::text, ', ' order by ccv.cross_chk_catalog_value_id ) as cross_chk_catalog_value_id_list
  from  camdecmpsmd.CROSS_CHECK_CATALOG ccc
        join camdecmpsmd.CROSS_CHECK_CATALOG_VALUE ccv using ( cross_chk_catalog_id )
 group
    by  ccc.cross_chk_catalog_id,
        ccc.cross_chk_catalog_name,
        ccv.value1,
        ccv.value2,
        ccv.value3
having  ( count( 1 ) > 1 )
 order
    by  ccc.cross_chk_catalog_id,
        ccc.cross_chk_catalog_name,
        ccv.value1,
        ccv.value2,
        ccv.value3;

Overview

The purpose of this ticket is twofold:

1. Eliminate duplicate rows in the CAMDECMPSMD CROSS_CHECK_CATALOG_VALUE table.
   • Create a deployment script to eliminate the duplicates in the database.
   • Ensure that the cross_check_catalog_value.sql metadata script is updated.
2. Add a unique constraint to the CAMDECMPSMD CROSS_CHECK_CATALOG_VALUE table.
   • Update camdecmpsmd\constraints-indexes\3-cross_check_catalog_value.sql DDL script

The unique constraint should be based on the following columns, and rows with the same values for these columns would be duplicates.

• CROSS_CHK_CATALOG_ID
• VALUE1
• VALUE2
• VALUE3

Acceptance Criteria

Based on the following columns:

• CROSS_CHK_CATALOG_ID
• VALUE1
• VALUE2
• VALUE3

1. Duplicated rows in the CAMDECMPSMD CROSS_CHECK_CATALOG_VALUE do not exist.
2. A unique constraint exists for CAMDECMPSMD CROSS_CHECK_CATALOG_VALUE.