SQL Queries - CAST Central Base - Queries on Objects - How to get the list of objects that are part of two snapshots but they are linked only to transactions of the previous snapshot
Purpose of Query
This page provide you the list of objects that are part of two snapshots but they are linked only to transactions of the previous or n-1 snapshot. That is the snapshot which is previous to the one in which the behavior is observed.
Applicable CAST Version
| Release | Yes/No |
|---|---|
| 8.3.x | (coche) |
| 8.2.x | (coche) |
Applicable RDBMS
| RDBMS | Yes/No |
|---|---|
| Oracle Server | (coche) |
| Microsoft SQL Server | (coche) |
| CSS2 | (coche) |
Query for CSS
SELECT previous_transaction_object
-- Transactiona object part of prevoius run but not the current run
FROM (
-- Transaction object in the previous run
SELECT OI.object_id AS Previous_Transaction_object
FROM dss_object_info OI
JOIN dss_link_info L
ON OI.object_id = L.next_object_id
AND OI.snapshot_id = L.snapshot_id
JOIN dss_objects DOB
ON DOB.object_id = OI.object_id
WHERE L.link_type_id BETWEEN 11002 AND 11006
AND OI.snapshot_id = <previous snapshot>) Previous_functional_area
LEFT JOIN (SELECT OI.object_id AS Currebt_Transaction_object
-- Transaction object in the current run
FROM dss_object_info OI
JOIN dss_link_info L
ON OI.object_id = L.next_object_id
AND OI.snapshot_id = L.snapshot_id
JOIN dss_objects DOB
ON DOB.object_id = OI.object_id
WHERE L.link_type_id BETWEEN 11002 AND 11006
AND OI.snapshot_id = <current snapshot>) Current_functional_area
ON current_functional_area = previous_functional_area
WHERE current_functional_area IS NULL
AND previous_transaction_object IN (
-- object that are part of the 2 snapshots
SELECT doi1.object_id
FROM dss_object_info doi1
JOIN dss_object_info doi2
ON doi1.object_id =
doi2.object_id
WHERE doi1.snapshot_id = <current snapshot>
AND doi2.snapshot_id = previous snapshot)
Query result example
–35432
Query result interpretation
The query returns the objects Id’s of objects that are part of two snapshots but they are linked only to transactions of the previous or n-1 snapshot
Query for Oracle
SELECT previous_transaction_object
-- Transactiona object part of prevoius run but not the current run
FROM (
-- Transaction object in the previous run
SELECT OI.object_id AS Previous_Transaction_object
FROM dss_object_info OI
JOIN dss_link_info L
ON OI.object_id = L.next_object_id
AND OI.snapshot_id = L.snapshot_id
JOIN dss_objects DOB
ON DOB.object_id = OI.object_id
WHERE L.link_type_id BETWEEN 11002 AND 11006
AND OI.snapshot_id = <previous snapshot>) Previous_functional_area
LEFT JOIN (SELECT OI.object_id AS Currebt_Transaction_object
-- Transaction object in the current run
FROM dss_object_info OI
JOIN dss_link_info L
ON OI.object_id = L.next_object_id
AND OI.snapshot_id = L.snapshot_id
JOIN dss_objects DOB
ON DOB.object_id = OI.object_id
WHERE L.link_type_id BETWEEN 11002 AND 11006
AND OI.snapshot_id = <current snapshot>) Current_functional_area
ON current_functional_area = previous_functional_area
WHERE current_functional_area IS NULL
AND previous_transaction_object IN (
-- object that are part of the 2 snapshots
SELECT doi1.object_id
FROM dss_object_info doi1
JOIN dss_object_info doi2
ON doi1.object_id =
doi2.object_id
WHERE doi1.snapshot_id = <current snapshot>
AND doi2.snapshot_id = previous snapshot)
Query result example
–35432
Query result interpretation
The query returns the objects Id’s of objects that are part of two snapshots but they are linked only to transactions of the previous or n-1 snapshot
Query for SQL Server
SELECT previous_transaction_object
-- Transactiona object part of prevoius run but not the current run
FROM (
-- Transaction object in the previous run
SELECT OI.object_id AS Previous_Transaction_object
FROM dss_object_info OI
JOIN dss_link_info L
ON OI.object_id = L.next_object_id
AND OI.snapshot_id = L.snapshot_id
JOIN dss_objects DOB
ON DOB.object_id = OI.object_id
WHERE L.link_type_id BETWEEN 11002 AND 11006
AND OI.snapshot_id = <previous snapshot>) Previous_functional_area
LEFT JOIN (SELECT OI.object_id AS Currebt_Transaction_object
-- Transaction object in the current run
FROM dss_object_info OI
JOIN dss_link_info L
ON OI.object_id = L.next_object_id
AND OI.snapshot_id = L.snapshot_id
JOIN dss_objects DOB
ON DOB.object_id = OI.object_id
WHERE L.link_type_id BETWEEN 11002 AND 11006
AND OI.snapshot_id = <current snapshot>) Current_functional_area
ON current_functional_area = previous_functional_area
WHERE current_functional_area IS NULL
AND previous_transaction_object IN (
-- object that are part of the 2 snapshots
SELECT doi1.object_id
FROM dss_object_info doi1
JOIN dss_object_info doi2
ON doi1.object_id =
doi2.object_id
WHERE doi1.snapshot_id = <current snapshot>
AND doi2.snapshot_id = previous snapshot)
Query result example
–35432
Query result interpretation
The query returns the objects Id’s of objects that are part of two snapshots but they are linked only to transactions of the previous or n-1 snapshot
Notes/comments
Related Pages