SQL Queries - CAST Knowledge Base - Queries on links - How to get links between objects

Purpose of Query

The query return shows whether links are there between objects

Applicable CAST Version

Release	Yes/No
8.3.x	✅
8.2.x	✅
8.1.x	✅
8.0.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	✅
CSS2	✅

Query for CSS

SELECT a.idacc         AS link_id, 
       kClr.idkey      AS CALLER_ID, 
       kClr.keynam     AS CALLER_NAME, 
       ofnClr.fullname AS CALLER_FULL_NAME, 
       tClr.typnam     AS CALLER_TYPE_NAME, 
       tClr.typdsc     AS CALLER_TYPE_DESCRIPTION, 
       kCle.idkey      AS CALLEE_ID, 
       kCle.keynam     AS CALLEE_NAME, 
       ofnCle.fullname AS CALLEE_FULL_NAME, 
       tCle.typnam     AS CALLEE_TYPE_NAME, 
       tCle.typdsc     AS CALLEE_TYPE_DESCRIPTION 
FROM   acc a 
       JOIN keys kClr 
         ON kClr.idkey = a.idclr 
       JOIN keys kCle 
         ON kCle.idkey = a.idcle 
       JOIN objfulnam ofnClr 
         ON ofnClr.idobj = kClr.idkey 
       JOIN objfulnam ofnCle 
         ON ofnCle.idobj = kCle.idkey 
       JOIN typ tClr 
         ON tClr.idtyp = kClr.objtyp 
       JOIN typ tCle 
         ON tCle.idtyp = kCle.objtyp 
WHERE  kClr.idkey = '<CALLER_OBJECT_ID>' 
       AND kCle.idkey = '<CALLEE_OBJECT_ID>';

Query result example

2756483;1038;“TARIFF_PACK”;“p1c1d234.GAIM.TARIFF_PACK”;“CAST_Oracle_PackageBody”;“Oracle package body”;1060;“TARIFF_PACK”;“p1c1d234.GAIM.TARIFF_PACK”;“CAST_Oracle_PackageHeader”;“Oracle package header”

Query result interpretation

If the query returns rows, then there are links, else there are no links

Query for Oracle

SELECT a.idacc         AS link_id, 
       kClr.idkey      AS CALLER_ID, 
       kClr.keynam     AS CALLER_NAME, 
       ofnClr.fullname AS CALLER_FULL_NAME, 
       tClr.typnam     AS CALLER_TYPE_NAME, 
       tClr.typdsc     AS CALLER_TYPE_DESCRIPTION, 
       kCle.idkey      AS CALLEE_ID, 
       kCle.keynam     AS CALLEE_NAME, 
       ofnCle.fullname AS CALLEE_FULL_NAME, 
       tCle.typnam     AS CALLEE_TYPE_NAME, 
       tCle.typdsc     AS CALLEE_TYPE_DESCRIPTION 
FROM   acc a 
       JOIN keys kClr 
         ON kClr.idkey = a.idclr 
       JOIN keys kCle 
         ON kCle.idkey = a.idcle 
       JOIN objfulnam ofnClr 
         ON ofnClr.idobj = kClr.idkey 
       JOIN objfulnam ofnCle 
         ON ofnCle.idobj = kCle.idkey 
       JOIN typ tClr 
         ON tClr.idtyp = kClr.objtyp 
       JOIN typ tCle 
         ON tCle.idtyp = kCle.objtyp 
WHERE  kClr.idkey = '<CALLER_OBJECT_ID>' 
       AND kCle.idkey = '<CALLEE_OBJECT_ID>';

Query result example

Query result interpretation

If the query returns rows, then there are links, else there are no links

Query for SQL server

SELECT a.idacc         AS link_id, 
       kClr.idkey      AS CALLER_ID, 
       kClr.keynam     AS CALLER_NAME, 
       ofnClr.fullname AS CALLER_FULL_NAME, 
       tClr.typnam     AS CALLER_TYPE_NAME, 
       tClr.typdsc     AS CALLER_TYPE_DESCRIPTION, 
       kCle.idkey      AS CALLEE_ID, 
       kCle.keynam     AS CALLEE_NAME, 
       ofnCle.fullname AS CALLEE_FULL_NAME, 
       tCle.typnam     AS CALLEE_TYPE_NAME, 
       tCle.typdsc     AS CALLEE_TYPE_DESCRIPTION 
FROM   acc a 
       JOIN keys kClr 
         ON kClr.idkey = a.idclr 
       JOIN keys kCle 
         ON kCle.idkey = a.idcle 
       JOIN objfulnam ofnClr 
         ON ofnClr.idobj = kClr.idkey 
       JOIN objfulnam ofnCle 
         ON ofnCle.idobj = kCle.idkey 
       JOIN typ tClr 
         ON tClr.idtyp = kClr.objtyp 
       JOIN typ tCle 
         ON tCle.idtyp = kCle.objtyp 
WHERE  kClr.idkey = '<CALLER_OBJECT_ID>' 
       AND kCle.idkey = '<CALLEE_OBJECT_ID>';

Query result example

Query result interpretation

If the query returns rows, then there are links, else there are no links

Notes/comments

Related Pages