SQL Queries - CAST Central Base - SQL Queries on Transactions - How to retrieve transactions shown in the TWRI Risk Indicators - Transaction Level view of the dashboard

Purpose of Query

The purpose of this query is to retrieve the transactions shown in the dashboard TWRI view (FRAME_PORTAL_TRANSACTION_VIEW - Risk Indicators - Transaction Level).

Applicable CAST Version

ReleaseYes/No
8.3.x✅

Applicable RDBMS

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

Query for CSS

For CAST version 8.3.16 or higher use this query (in 8.3.16 the link information was moved from the DSS_LINK_INFO table to the FP_LINK_INFO table):

SELECT o.OBJECT_FULL_NAME ,
o.OBJECT_ID AS TRANSACTION_ID,
t.RANKING
FROM (SELECT o.OBJECT_ID,
o.OBJECT_FULL_NAME
FROM DSS_OBJECTS o
WHERE o.OBJECT_TYPE_ID = 30002
)
AS o
JOIN
(SELECT DISTINCT l.PREVIOUS_OBJECT_ID
FROM FP_LINK_INFO l
WHERE l.LINK_TYPE_ID = 11004
/* Link on table must exists */
AND l.SNAPSHOT_ID = <snapshot_id>
)
AS l
ON l.PREVIOUS_OBJECT_ID = o.OBJECT_ID
JOIN
( SELECT transaction_detail.PREVIOUS_OBJECT_ID,
SUM(r.VIOLATION_INDEX) AS RANKING
FROM (SELECT DISTINCT r.OBJECT_ID,
r.VIOLATION_INDEX
FROM DSS_SNAPSHOT_RANKING r
WHERE r.SNAPSHOT_ID = <snapshot_id>
AND r.BUSINESS_CRITERION_ID = 60013
)
AS r
JOIN
(SELECT DISTINCT transaction_detail.NEXT_OBJECT_ID,
transaction_detail.PREVIOUS_OBJECT_ID
FROM FP_LINK_INFO AS transaction_detail
WHERE transaction_detail.SNAPSHOT_ID = <snapshot_id>
AND transaction_detail.LINK_TYPE_ID BETWEEN 11002 AND 11005
)
AS transaction_detail
ON r.OBJECT_ID = transaction_detail.NEXT_OBJECT_ID
GROUP BY transaction_detail.PREVIOUS_OBJECT_ID
)
t
ON t.PREVIOUS_OBJECT_ID = o.OBJECT_ID
ORDER BY 3 DESC

For CAST version 8.3.15 or lower use this query:

SELECT o.OBJECT_FULL_NAME , o.OBJECT_ID AS TRANSACTION_ID, t.RANKING  FROM 
(SELECT o.OBJECT_ID,
 o.OBJECT_FULL_NAME
 FROM DSS_OBJECTS o
 WHERE o.OBJECT_TYPE_ID = 30002 ) AS o 
JOIN
 (SELECT DISTINCT l.PREVIOUS_OBJECT_ID
 FROM DSS_LINK_INFO l
 WHERE l.LINK_TYPE_ID = 11004 /* Link on table must exists */
 AND l.SNAPSHOT_ID = <snapshot_id> ) AS l 
ON l.PREVIOUS_OBJECT_ID = o.OBJECT_ID
 JOIN ( SELECT transaction_detail.PREVIOUS_OBJECT_ID, SUM(r.VIOLATION_INDEX)
 AS RANKING
 FROM (SELECT DISTINCT r.OBJECT_ID,
 r.VIOLATION_INDEX
 FROM DSS_SNAPSHOT_RANKING r
 WHERE r.SNAPSHOT_ID = <snapshot_id>
 AND r.BUSINESS_CRITERION_ID = 60013 ) AS r
 JOIN (SELECT DISTINCT transaction_detail.NEXT_OBJECT_ID,
 transaction_detail.PREVIOUS_OBJECT_ID
 FROM DSS_LINK_INFO AS transaction_detail
 WHERE transaction_detail.SNAPSHOT_ID = <snapshot_id>
 AND transaction_detail.LINK_TYPE_ID BETWEEN 11002 AND 11005 )
 AS transaction_detail
 ON r.OBJECT_ID = transaction_detail.NEXT_OBJECT_ID
 GROUP BY transaction_detail.PREVIOUS_OBJECT_ID ) t 
ON t.PREVIOUS_OBJECT_ID = o.OBJECT_ID
 ORDER BY 3 DESC

Query for Oracle

Same as Above

Query for SQL server

Same As above

Notes/comments

The appropriate snapshot_id can be obtained by running on the central the query:

 select * from dss_snapshots

Related Pages