CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - More than one row returned by a subquery used as an expression

The purpose of this page is to troubleshoot errors in snapshot generation in the consolidation phase for the below-mentioned error.

• See the Cast-MS log file to check the error and SQL statement.
• Is the error "ERROR: more than one row returned by a subquery used as an expression" with the below SQL statement failing:
   

ERROR: more than one row returned by a subquery used as an expression
                              Where: SQL statement "Update ADG_DELTA_SNAPSHOTS
                              set LAST_SNAPSHOT_ID = (select s.SNAPSHOT_ID from ADG_DELTA_SNAPSHOTS s
                              where s.APPLICATION_ID = ADG_DELTA_SNAPSHOTS.APPLICATION_ID
                              and s.LATEST = 1),
                              LAST_TEXT_DATE = (select s.TEXT_DATE from ADG_DELTA_SNAPSHOTS s
                              where s.APPLICATION_ID = ADG_DELTA_SNAPSHOTS.APPLICATION_ID
                              and s.LATEST = 1)
                              where exists (select 1 from ADG_DELTA_SNAPSHOTS s
                              where s.APPLICATION_ID = ADG_DELTA_SNAPSHOTS.APPLICATION_ID
                              and s.LATEST = 1)"
                              PL/pgSQL function adg_compute_delta_snapshots(integer) line 111 at SQL statement
                              SQL statement "select  ADG_COMPUTE_DELTA_SNAPSHOTS (31)"
                              PL/pgSQL function dss_execute_named_side_process(integer,character varying,integer) line 64 at EXECUTE statement
                              SQL statement "select DSS_EXECUTE_NAMED_SIDE_PROCESS(I_SNAPSHOT_ID, 'ALL', I_SIDE)"
                              PL/pgSQL function dss_execute_side_process(integer,integer) line 5 at SQL statement
                              PL/pgSQL function dss_cleanup_compute_metric(integer) line 6 at assignment

• Is the Cast-MS log containing the same failing SQL statement, if yes then the issue is due to more than one snapshot bearing the same functional date?
  As a result, the snapshot fails when it consolidates the previous and current snapshot to compute the added/deleted artifacts, as it finds more than one snapshot with the same date.
• The functional data redundancy is due to manual updates for either of the snapshots since it's not possible to generate two snapshots with the same functional date from CMS.

1. Package and deliver the source code via DMT
2. Accept the delivery
3. Run the analysis and run snapshot twice

1.  Import the central base 

2.  Run the following queries to get the snapshot details
   

   Select * from DSS_SNAPSHOTS;

3. Identify the snapshots having the same functional date.
   For example, snapshot id 30 and 31 have the same functional date. 

4. Run the following query on central base to update either functional_date:
   Note: The date mentioned below is an example, so you need to update the date to be unique as per the result of your query.
   The date should not be greater than that of the current snapshot as it will treat the previous snapshot as the current and lead to inconsistent results.
   

   Update DSS_SNAPSHOTS set functional date = '1/25/2014 1:05:00' where snapshot_id =30;

   
   

5. Once updated, you can log-in to CMS and try to take a snapshot which will prompt you to continue the already running snapshot.
   You should continue running the already running snapshot which should run successfully
6. If the above steps do not solve the issue, get in touch with Cast Support with the following Relevant Input