SQL Queries - CAST Knowledge Base - Corruptions - Corruptions on datafunctions - How to find the Datafunction ID which belong to a Tree of merged items

The SQL query in this page lists the Datafunctions id that are part of a nested merge.

For example a datafunction being both a merge root for one merged item and merged item for another merge root is part of a nested merge.   

• The Datafunction being a merge root is the data function that has been merged with others, and it is the root of the merge, so it appears on the dashboard and TCC)
• The Datafunction being a merged item is the data function that has been merged as a child of another one, so it does not appear in the dashboard and TCC

SELECT DF2.object_id 
FROM   dss_datafunction DF1, 
       dss_datafunction DF2 
WHERE  ( DF1.object_id != DF1.cal_mergeroot_id ) 
       AND ( ( DF1.cal_flags & 6 ) != 6 ) 
       AND ( DF2.object_id != DF2.cal_mergeroot_id ) 
       AND ( ( DF2.cal_flags & 6 ) != 6 ) 
       AND ( ( DF1.cal_flags & 4 ) = 4 ) 
       AND ( DF2.object_id = DF1.cal_mergeroot_id ) 
       AND ( ( DF2.cal_flags & 4 ) = 4 ) 
ORDER  BY DF1.object_id; 

The remediation is as follow:

1. Create table fp_wk_calib_issues as follows:

   Remediation

   CREATE TABLE fp_wk_calib_issues 
                ( 
                             appli_id INTEGER NOT NULL, 
                             description CHARACTER varying(500) NOT NULL, 
                             calib_issue_num serial NOT NULL 
                ) 
                WITH 
                ( 
                             oids=FALSE 
                );ALTER TABLE fp_wk_calib_issues owner TO operator;

2. Create function CAST_LOG as follow:

   Remediation

   CREATE OR REPLACE function 
     cast_log(message VARCHAR) 
     returns void 
   AS 
     $body$ 
   begin 
    
     INSERT INTO dss_history 
                 ( 
                             description 
                 ) 
                 VALUES 
                 ( 
                             LEFT(message,500) 
                 ); -- avoid SQL Error "value too long for type character varying(500)"END;$body$ 
   language 'plpgsql'

3. Install the following script DSSAPP_FP_FIX_CALIB_ISSUES.sql