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

The purpose of this page is to troubleshoot the error in snapshot deletion Error while executing procedure - More than one row returned by a subquery used as an expression

See the Cast-MS log file to check the error and sql statement.

    ERROR: more than one row returned by a subquery used as an expression
      Where: SQL statement "Update ADG_DELTA_SNAPSHOTS
      set FIRST_SNAPSHOT_ID = (select s.SNAPSHOT_ID
      from ADG_DELTA_SNAPSHOTS s
      where s.APPLICATION_ID = ADG_DELTA_SNAPSHOTS.APPLICATION_ID
      and s.TEXT_DATE = ADG_DELTA_SNAPSHOTS.FIRST_TEXT_DATE
      )"

      PL/pgSQL function adg_compute_delta_snapshots(integer) line 69 at SQL statement
      SQL statement "select  ADG_COMPUTE_DELTA_SNAPSHOTS (13)"
      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
      SQL statement "select DSS_EXECUTE_SIDE_PROCESS(I_SNAPSHOT_ID, -1)"
      PL/pgSQL function dss_purge_snapshot(integer) line 139 at SQL statement

• If the CAST-MS log contains the same failing sql statement, then the issue is due to more than one snapshot bearing the same functional date.
  After deletion of a snapshot, CAST-MS will re-consolidate the previous and the next snapshots and compute the added/deleted artifacts, then it fails as it finds more than one snapshot with the same date.
• It is not possible to generate two snapshots with same functional date from CAST-MS., but the check is not done in CAST-MS-CLI : this is a known issue that will be fixed in 8.2.7

1. Open Cast-MS, tab Execute
2. Choose a snapshot to delete, click on button Delete and confirm in pop-up

1. To proceed we require the following Relevant input
2. Import the central base 

3. Run the following queries to get the snapshot details;

   Select * from DSS_SNAPSHOTS;

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

5. Run the following query on central base to update the 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 to 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;

6. You can now delete the snapshot you originally wanted to delete 

7. If this does not solve the issue, get in touch with Cast Support with Relevant Input.

Relevant input

• Sherlock, with options Export Logs, and Export databases for Central Base and Management Base only
  
• Screenshots from CMS to show the crash of snapshot (Expand the UI window to show all the steps of the snapshot and the step of crash)