SQL Queries - CAST Knowledge Base - Queries on Links - How to count the internal links for each project

Purpose of Query

The query retrieves the number of internal links on the Knowledge base.

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅

Applicable RDBMS

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

Query for CSS

SELECT ot.objtypstr, 
       k.keynam, 
       CASE accknd 
         WHEN 0 THEN 'classic' 
         ELSE 'escalated' 
       END, 
       Count(1) 
FROM   objtypstr ot, 
       keys k, 
       objpro op, 
       acc a 
WHERE  a.idpro = k.idkey 
       AND k.objtyp = ot.objtyp 
       AND a.idcle = op.idobj 
       AND op.idpro = a.idpro 
GROUP  BY ot.objtypstr, 
          k.keynam, 
          accknd

Query result example

“C++ Project”;“GUI_13822_Cpp”;“classic”;732
“C++ Project”;“Db_13827_C”;“classic”;28821
“C++ Project”;“A_lib_13829_Cpp”;“classic”;947
“C++ Project”;“NetworkBuild_13826_Cpp”;“classic”;233
“C++ Project”;“NB_TEST_13828_Cpp”;“classic”;29
“C++ Project”;“Common_13824_Cpp”;“classic”;484

Query result interpretation

The query return the count of internal links for each project

Query for Oracle

SELECT ot.objtypstr, 
       k.keynam, 
       CASE accknd 
         WHEN 0 THEN 'classic' 
         ELSE 'escalated' 
       END, 
       Count(1) 
FROM   objtypstr ot, 
       keys k, 
       objpro op, 
       acc a 
WHERE  a.idpro = k.idkey 
       AND k.objtyp = ot.objtyp 
       AND a.idcle = op.idobj 
       AND op.idpro = a.idpro 
GROUP  BY ot.objtypstr, 
          k.keynam, 
          accknd

Query result example

“C++ Project”;“GUI_13822_Cpp”;“classic”;732
“C++ Project”;“Db_13827_C”;“classic”;28821
“C++ Project”;“A_lib_13829_Cpp”;“classic”;947
“C++ Project”;“NetworkBuild_13826_Cpp”;“classic”;233
“C++ Project”;“NB_TEST_13828_Cpp”;“classic”;29
“C++ Project”;“Common_13824_Cpp”;“classic”;484

Query result interpretation

The query return the count of internal links for each project

Query for SQL server

SELECT ot.objtypstr, 
       k.keynam, 
       CASE accknd 
         WHEN 0 THEN 'classic' 
         ELSE 'escalated' 
       END, 
       Count(1) 
FROM   objtypstr ot, 
       keys k, 
       objpro op, 
       acc a 
WHERE  a.idpro = k.idkey 
       AND k.objtyp = ot.objtyp 
       AND a.idcle = op.idobj 
       AND op.idpro = a.idpro 
GROUP  BY ot.objtypstr, 
          k.keynam, 
          accknd

Query result example

“C++ Project”;“GUI_13822_Cpp”;“classic”;732
“C++ Project”;“Db_13827_C”;“classic”;28821
“C++ Project”;“A_lib_13829_Cpp”;“classic”;947
“C++ Project”;“NetworkBuild_13826_Cpp”;“classic”;233
“C++ Project”;“NB_TEST_13828_Cpp”;“classic”;29
“C++ Project”;“Common_13824_Cpp”;“classic”;484

Query result interpretation

The query return the count of internal links for each project.

Notes/comments

Related Pages