SQL Queries - CAST Central Base - Queries on Objects - How to get the object status

Purpose of Query

To get the status of a given object the following query can be run on the central repository.

Applicable CAST Version

ReleaseYes/No
8.2.x✅
8.1.x✅
8.0.x✅
7.3.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server❓
Microsoft SQL Server❓
CSS2✅

Query for CSS

SELECT CASE COALESCE((SELECT DISTINCT 1 
                      FROM   dss_object_info DOI, 
                             dss_object_info PREV_DOI, 
                             adg_delta_snapshots ads_prev 
                      WHERE  DOI.object_id = <object_id> 
                             AND PREV_DOI.object_id = <object_id>
                             AND DOI.snapshot_id = ads_prev.snapshot_id 
                             AND ads_prev.application_id = 100 
                             AND ads_prev.prev_snapshot_id = 
                                 PREV_DOI.snapshot_id 
                             AND PREV_DOI.object_checksum = 
            DOI.object_checksum), 0) 
         WHEN 1 THEN 'unchanged object' 
         WHEN 0 THEN COALESCE((SELECT DISTINCT 'updated object' 
                               FROM   dss_object_info DOI, 
                                      dss_object_info PREV_DOI, 
                                      adg_delta_snapshots ads_prev 
                               WHERE  DOI.object_id = 10 
                                      AND PREV_DOI.object_id = 10 
                                      AND DOI.snapshot_id = ads_prev.snapshot_id 
                                      AND ads_prev.application_id = 100 
                                      AND ads_prev.prev_snapshot_id = 
                                          PREV_DOI.snapshot_id 
                                      AND PREV_DOI.object_checksum != 
                                   DOI.object_checksum), 'added object') 
       END "OBJECT_STATUS"

Query result example

“added object”

Query result interpretation

In this example we have replace the <object_id> by integer 4365 in order to get the status of the object that have the id 4365, in this case this object is an added one.

Query for Oracle

Enter the SQL query

Query result example

Query result interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages