SQL Queries - CAST Central Base - Queries on Objects - How to check if the objects present in the previous snapshot but not current snapshot are part of one of the Transactions in previous snapshot

Purpose of Query

To check if the objects present in the previous snapshot but not current snapshot are part of one of the Transactions in previous snapshot. The following query can be run on the central repository.

Applicable CAST Version

Release	Yes/No
8.3.x	✅
8.2.x	✅
8.1.x	✅
8.0.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	✅
CSS2	✅

Query for CSS

SELECT count_technical_delta,
       count_functional_technical_delta 
FROM   (SELECT Count (previous_run.object_id) AS count_technical_delta
        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 run>
                       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 run>
                                  AND doi.object_checksum != 0)previous_run
                       ON current_run.object_id = previous_run.object_id
        WHERE  current_run.object_id IS NULL) SET_technical_delta,
       (SELECT Count (OI.object_id) AS count_functional_technical_delta
        FROM   dss_object_info OI,
               dss_link_info L
        WHERE  L.link_type_id BETWEEN 11002 AND 11006
               AND OI.object_id = L.next_object_id
               AND OI.snapshot_id = L.snapshot_id
               AND OI.snapshot_id = <current run>
               AND OI.object_id IN (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 run>
                                                   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 = <prevvious run>
AND doi.object_checksum
    != 0)
previous_run
ON current_run.object_id =
previous_run.object_id
WHERE  current_run.object_id IS NULL))
SET_functional_technical_delta

Query result example

–10; 10

Query result interpretation

The query returns

count1 = The count of objects that are part of previous run but not current run
count2 = The count of objects that are part of previous run but not current run and was part of the functional area of the application

Query for Oracle

SELECT count_technical_delta,
       count_functional_technical_delta 
FROM   (SELECT Count (previous_run.object_id) AS count_technical_delta
        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 run>
                       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 run>
                                  AND doi.object_checksum != 0)previous_run
                       ON current_run.object_id = previous_run.object_id
        WHERE  current_run.object_id IS NULL) SET_technical_delta,
       (SELECT Count (OI.object_id) AS count_functional_technical_delta
        FROM   dss_object_info OI,
               dss_link_info L
        WHERE  L.link_type_id BETWEEN 11002 AND 11006
               AND OI.object_id = L.next_object_id
               AND OI.snapshot_id = L.snapshot_id
               AND OI.snapshot_id = <current run>
               AND OI.object_id IN (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 run>
                                                   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 = <prevvious run>
AND doi.object_checksum
    != 0)
previous_run
ON current_run.object_id =
previous_run.object_id
WHERE  current_run.object_id IS NULL))
SET_functional_technical_delta

Query result example

–10; 10

Query result interpretation

The query returns

count1 = The count of objects that are part of previous run but not current run
count2 = The count of objects that are part of previous run but not current run and was part of the functional area of the application

Query for SQL server

SELECT count_technical_delta,
       count_functional_technical_delta 
FROM   (SELECT Count (previous_run.object_id) AS count_technical_delta
        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 run>
                       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 run>
                                  AND doi.object_checksum != 0)previous_run
                       ON current_run.object_id = previous_run.object_id
        WHERE  current_run.object_id IS NULL) SET_technical_delta,
       (SELECT Count (OI.object_id) AS count_functional_technical_delta
        FROM   dss_object_info OI,
               dss_link_info L
        WHERE  L.link_type_id BETWEEN 11002 AND 11006
               AND OI.object_id = L.next_object_id
               AND OI.snapshot_id = L.snapshot_id
               AND OI.snapshot_id = <current run>
               AND OI.object_id IN (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 run>
                                                   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 = <prevvious run>
AND doi.object_checksum
    != 0)
previous_run
ON current_run.object_id =
previous_run.object_id
WHERE  current_run.object_id IS NULL))
SET_functional_technical_delta

Query result example

–10; 10

Query result interpretation

The query returns

count1 = The count of objects that are part of previous run but not current run
count2 = The count of objects that are part of previous run but not current run and was part of the functional area of the application

Notes/comments

Related Pages