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
- CAST AIP 8.3 : TCC - Enhancement node - Right hand panel
- CAST AIP 8.2 : TCC - Enhancement node - Right hand panel
- CAST AIP 8.1 : TCC - EFP Calibration node - Right hand panel
- CAST AIP 8.0 : TCC - EFP Calibration node - Right hand panel
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
-- 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