Purpose of Query
This page provide you the list of objects that are part of two snapshots but they are linked to only transactions of the current snapshot. Current is the snapshot in which the behavior was 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 current_transaction_object
-- Transactiona object part of current run but not the previous run
FROM (
-- Transaction object in the current run
SELECT OI.object_id AS Current_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 = <current snapshot_id>) Current_functional_area
LEFT JOIN (SELECT OI.object_id AS Previous_Transaction_object
-- Transaction object in the previous 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 = <previous snapshot_id>) Previous_functional_area
ON current_functional_area = previous_functional_area
WHERE previous_functional_area IS NULL
AND current_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_id>
AND doi2.snapshot_id = <previous_snapshot_id>)
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 current snapshot.
Query for Oracle
SELECT current_transaction_object
-- Transactiona object part of current run but not the previous run
FROM (
-- Transaction object in the current run
SELECT OI.object_id AS Current_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 = <current snapshot_id>) Current_functional_area
LEFT JOIN (SELECT OI.object_id AS Previous_Transaction_object
-- Transaction object in the previous 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 = <previous snapshot_id>) Previous_functional_area
ON current_functional_area = previous_functional_area
WHERE previous_functional_area IS NULL
AND current_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_id>
AND doi2.snapshot_id = <previous_snapshot_id>)
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 current snapshot.
Query for SQL Server
SELECT current_transaction_object
-- Transactiona object part of current run but not the previous run
FROM (
-- Transaction object in the current run
SELECT OI.object_id AS Current_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 = <current snapshot_id>) Current_functional_area
LEFT JOIN (SELECT OI.object_id AS Previous_Transaction_object
-- Transaction object in the previous 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 = <previous snapshot_id>) Previous_functional_area
ON current_functional_area = previous_functional_area
WHERE previous_functional_area IS NULL
AND current_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_id>
AND doi2.snapshot_id = <previous_snapshot_id>)
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 current snapshot.
Notes/comments
Related Pages
