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 |  |
| 8.2.x | |
Applicable RDBMS
RDBMS | Yes/No |
|---|---|
| Oracle Server | |
| Microsoft SQL Server | |
| CSS2 | |
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
