SQL Queries - CAST Central Base - Queries on Objects - How to check if the deleted technical objects has the Deleted status


Purpose of Query

To check if the objects technically Deleted has the technical status "Deleted". The following query can be run on the central repository.

Official documentation

Applicable CAST Version
Release
Yes/No
8.3.x (tick) 
8.2.x (tick) 
8.1.x (error)
8.0.x (error)
Applicable RDBMS

 

RDBMS
Yes/No
Oracle Server  (tick)
Microsoft SQL Server  (tick)
CSS2  (tick)


Query for CSS
-- Metric level
SELECT a.snapshot_id,
       a.object_id,
       CASE a.metric_id
         WHEN 10351 THEN 'ADDED' --3751 ms.
         WHEN 10352 THEN 'DELETED'
         WHEN 10353 THEN 'UPDATED'
         WHEN 10354 THEN 'UNCHANGED'
       END AS STATUS
FROM   dss_metric_results a
       JOIN dss_objects o
         ON o.object_id = a.object_id
            AND o.object_type_id != -102
       JOIN dss_snapshots s
         ON s.snapshot_id = a.snapshot_id
WHERE  a.metric_id IN ( 10351, 10352, 10353, 10354 )
       AND s.snapshot_status = 2
       AND s.enhancement_measure = 'AEP'
       AND a.metric_value_index = 1
       AND a.snapshot_id = <current snapshot id>
       -- except functional 
       AND NOT EXISTS (SELECT 1
                       FROM   efp_tran_info t
                              JOIN dss_link_info l
                                ON l.previous_object_id = t.object_id
                       WHERE  ( ( a.metric_id = 10352
                                  AND l.snapshot_id = Dss_get_previous_snapshot
                                                      (a.snapshot_id)
                                )
                                 OR ( a.metric_id != 10352
                                      AND l.snapshot_id = a.snapshot_id ) )
                              AND l.link_type_id BETWEEN 11002 AND 11006
                              AND t.snapshot_id = a.snapshot_id
                              AND t.status IS NOT NULL
                              AND a.object_id = l.next_object_id
                              AND NOT EXISTS (SELECT 1
                                              FROM   efp_tran_exclusion E
                                              WHERE  E.object_id = a.object_id))
       AND a.object_id IN (
                          -- Pure Delta
                          SELECT previous_run.object_id
                           FROM   (SELECT doi.snapshot_id,
                                          doi.object_id,
                                          dob.object_full_name
                                   FROM   dss_objects dob
                                          JOIN dss_object_info doi
                                            ON dob.object_id = doi.object_id
                                   WHERE  doi.snapshot_id = <current snapshot id>
                                          AND doi.object_checksum != 0)
                                  current_run
                                  RIGHT JOIN (SELECT doi.snapshot_id,
                                                     doi.object_id,
                                                     dob.object_full_name
                                              FROM   dss_objects dob
                                                     JOIN dss_object_info doi
                                                       ON dob.object_id =
                                                          doi.object_id
                                              WHERE  doi.snapshot_id = <previous snapshot id>
                                                     AND doi.object_checksum !=
                                                         0)
                                             previous_run
                                          ON current_run.object_id =
                                             previous_run.object_id
                           WHERE  current_run.object_id IS NULL)
ORDER  BY status
Query result example
 -- 5;2550;"DELETED"
Query result interpretation
 The query returns the id of the snapshot from where the object is deleted, the object id and the object status


Query for Oracle
-- Metric level
SELECT a.snapshot_id,
       a.object_id,
       CASE a.metric_id
         WHEN 10351 THEN 'ADDED' --3751 ms.
         WHEN 10352 THEN 'DELETED'
         WHEN 10353 THEN 'UPDATED'
         WHEN 10354 THEN 'UNCHANGED'
       END AS STATUS
FROM   dss_metric_results a
       JOIN dss_objects o
         ON o.object_id = a.object_id
            AND o.object_type_id != -102
       JOIN dss_snapshots s
         ON s.snapshot_id = a.snapshot_id
WHERE  a.metric_id IN ( 10351, 10352, 10353, 10354 )
       AND s.snapshot_status = 2
       AND s.enhancement_measure = 'AEP'
       AND a.metric_value_index = 1
       AND a.snapshot_id = <current snapshot id>
       -- except functional 
       AND NOT EXISTS (SELECT 1
                       FROM   efp_tran_info t
                              JOIN dss_link_info l
                                ON l.previous_object_id = t.object_id
                       WHERE  ( ( a.metric_id = 10352
                                  AND l.snapshot_id = Dss_get_previous_snapshot
                                                      (a.snapshot_id)
                                )
                                 OR ( a.metric_id != 10352
                                      AND l.snapshot_id = a.snapshot_id ) )
                              AND l.link_type_id BETWEEN 11002 AND 11006
                              AND t.snapshot_id = a.snapshot_id
                              AND t.status IS NOT NULL
                              AND a.object_id = l.next_object_id
                              AND NOT EXISTS (SELECT 1
                                              FROM   efp_tran_exclusion E
                                              WHERE  E.object_id = a.object_id))
       AND a.object_id IN (
                          -- Pure Delta
                          SELECT previous_run.object_id
                           FROM   (SELECT doi.snapshot_id,
                                          doi.object_id,
                                          dob.object_full_name
                                   FROM   dss_objects dob
                                          JOIN dss_object_info doi
                                            ON dob.object_id = doi.object_id
                                   WHERE  doi.snapshot_id = <current snapshot id>
                                          AND doi.object_checksum != 0)
                                  current_run
                                  RIGHT JOIN (SELECT doi.snapshot_id,
                                                     doi.object_id,
                                                     dob.object_full_name
                                              FROM   dss_objects dob
                                                     JOIN dss_object_info doi
                                                       ON dob.object_id =
                                                          doi.object_id
                                              WHERE  doi.snapshot_id = <previous snapshot id>
                                                     AND doi.object_checksum !=
                                                         0)
                                             previous_run
                                          ON current_run.object_id =
                                             previous_run.object_id
                           WHERE  current_run.object_id IS NULL)
ORDER  BY status
Query result example
 -- 5;2550;"DELETED"
Query result interpretation
 The query returns the id of the snapshot from where the object is deleted, the object id and the object status


Query for SQL server
-- Metric level
SELECT a.snapshot_id,
       a.object_id,
       CASE a.metric_id
         WHEN 10351 THEN 'ADDED' --3751 ms.
         WHEN 10352 THEN 'DELETED'
         WHEN 10353 THEN 'UPDATED'
         WHEN 10354 THEN 'UNCHANGED'
       END AS STATUS
FROM   dss_metric_results a
       JOIN dss_objects o
         ON o.object_id = a.object_id
            AND o.object_type_id != -102
       JOIN dss_snapshots s
         ON s.snapshot_id = a.snapshot_id
WHERE  a.metric_id IN ( 10351, 10352, 10353, 10354 )
       AND s.snapshot_status = 2
       AND s.enhancement_measure = 'AEP'
       AND a.metric_value_index = 1
       AND a.snapshot_id = <current snapshot id>
       -- except functional 
       AND NOT EXISTS (SELECT 1
                       FROM   efp_tran_info t
                              JOIN dss_link_info l
                                ON l.previous_object_id = t.object_id
                       WHERE  ( ( a.metric_id = 10352
                                  AND l.snapshot_id = Dss_get_previous_snapshot
                                                      (a.snapshot_id)
                                )
                                 OR ( a.metric_id != 10352
                                      AND l.snapshot_id = a.snapshot_id ) )
                              AND l.link_type_id BETWEEN 11002 AND 11006
                              AND t.snapshot_id = a.snapshot_id
                              AND t.status IS NOT NULL
                              AND a.object_id = l.next_object_id
                              AND NOT EXISTS (SELECT 1
                                              FROM   efp_tran_exclusion E
                                              WHERE  E.object_id = a.object_id))
       AND a.object_id IN (
                          -- Pure Delta
                          SELECT previous_run.object_id
                           FROM   (SELECT doi.snapshot_id,
                                          doi.object_id,
                                          dob.object_full_name
                                   FROM   dss_objects dob
                                          JOIN dss_object_info doi
                                            ON dob.object_id = doi.object_id
                                   WHERE  doi.snapshot_id = <current snapshot id>
                                          AND doi.object_checksum != 0)
                                  current_run
                                  RIGHT JOIN (SELECT doi.snapshot_id,
                                                     doi.object_id,
                                                     dob.object_full_name
                                              FROM   dss_objects dob
                                                     JOIN dss_object_info doi
                                                       ON dob.object_id =
                                                          doi.object_id
                                              WHERE  doi.snapshot_id = <previous snapshot id>
                                                     AND doi.object_checksum !=
                                                         0)
                                             previous_run
                                          ON current_run.object_id =
                                             previous_run.object_id
                           WHERE  current_run.object_id IS NULL)
ORDER  BY status
Query result example
 -- 5;2550;"DELETED"
Query result interpretation
 The query returns the id of the snapshot from where the object is deleted, the object id and the object status
Notes/comments
 

  

Related Pages