SQL Queries - CAST Central Base - SQL Queries on Transactions - How to retrieve the status of Transactions per snapshot

Purpose of Query

The query retrieve the status of Transactions per snapshot

Applicable CAST Version

Release	Yes/No
8.3.x	(coche)
8.2.x	(coche)
8.1.x	(coche)
8.0.x	(coche)

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	(coche)
Microsoft SQL Server	(coche)
CSS2	(coche)

Query for CSS

SELECT R.snapshot_id, 
       R.object_id,       
       CASE 
         WHEN OI.status IS NULL THEN 'UNKNOWN' 
         ELSE OI.status 
       END 
FROM   efp_tran_info OI, 
       dss_metric_results R, 
       dss_snapshots S 
WHERE  S.application_id = 3 
       AND R.snapshot_id = S.snapshot_id 
       AND R.metric_id IN ( 10322, 10204 ) 
       AND R.metric_value_index = 1 
       AND R.object_id = OI.object_id 
       AND R.snapshot_id = OI.snapshot_id 
ORDER  BY 1, 
          2

Query result example

1;920028;"ADDED"
2;920028;1;"DELETED"

Query result interpretation

The query returns the snapshot id, Transaction and Transaction status

Query for Oracle

SELECT R.snapshot_id, 
       R.object_id,       
       CASE 
         WHEN OI.status IS NULL THEN 'UNKNOWN' 
         ELSE OI.status 
       END 
FROM   efp_tran_info OI, 
       dss_metric_results R, 
       dss_snapshots S 
WHERE  S.application_id = 3 
       AND R.snapshot_id = S.snapshot_id 
       AND R.metric_id IN ( 10322, 10204 ) 
       AND R.metric_value_index = 1 
       AND R.object_id = OI.object_id 
       AND R.snapshot_id = OI.snapshot_id 
ORDER  BY 1, 
          2

Query result example

1;920028;"ADDED"
2;920028;1;"DELETED"

Query result interpretation

The query returns the snapshot id, Transaction and Transaction status

Query for SQL server

SELECT R.snapshot_id, 
       R.object_id,       
       CASE 
         WHEN OI.status IS NULL THEN 'UNKNOWN' 
         ELSE OI.status 
       END 
FROM   efp_tran_info OI, 
       dss_metric_results R, 
       dss_snapshots S 
WHERE  S.application_id = 3 
       AND R.snapshot_id = S.snapshot_id 
       AND R.metric_id IN ( 10322, 10204 ) 
       AND R.metric_value_index = 1 
       AND R.object_id = OI.object_id 
       AND R.snapshot_id = OI.snapshot_id 
ORDER  BY 1, 
          2

Query result example

1;920028;"ADDED"
2;920028;1;"DELETED"

Query result interpretation

The query returns the snapshot id, Transaction and Transaction status

Notes/comments

Related Pages