SQL Queries - CAST Management Base - How to uncheck the Analysis Units by running queries on Mngt for Oracle reports rather than unchecking it manually from the GUI

Created by John McMahon on Feb 11, 2022
Purpose of Query

This page describes how to uncheck the Analysis Units by running queries on the management database for Oracle reports rather than unchecking it manually from the GUI.

Applicable CAST Version
Release
Yes/No
8.3.x(tick)
8.2.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(question)
Microsoft SQL Server(question)
CSS(tick)
Query for CSS

The data is stored in the generic CMS_DynamicFields table for Oracle forms Reports. In this table, the fields which belongs to any entity of CAST-MS which is not mapped to any specific table are stored:


  1.  Entity_GUID column - corresponds to the type of the entity


  2.  Field_GUID column -  corresponds to the name of the field (i.e., the column in a specific table)


  3.  Object_ID column -  corresponds to the ID of the entity


  4.  Field_Value column -  corresponds to the value of the corresponding field for the corresponding entity


a) If we need to uncheck all the Analysis Units for Oracle forms reports then run the following query 


UPDATE CMS_DynamicFields
SET    Field_Value = '0'
WHERE  Entity_GUID = 'forms.FORMSReportsAnalysisUnit'
AND    Field_GUID  = 'Active'
Query result example
 Result should be success with the number of rows updated
Query result interpretation
 No interpretation required.

b) If we need to uncheck only certain analysis Units

    1) Run a SELECT query on that specific Analysis Unit. For Example: If the Analysis Unit that you are looking for is "KYT4_YCH.rdf"


SELECT *
FROM   CMS_DynamicFields
WHERE  field_value LIKE '%KYT4_YCH.rdf'
Query result example

139742;"forms.FORMSReportsAnalysisUnit";"name";"KYT4_YCH.rdf";""

Query result interpretation

Columns in the result are: OBJECT_ID;ENTITY_GUID;FIELD_GUID;FIELD_VALUE;FIELD_DATE


2) Pick the Object ID for the corresponding Analysis Unit that you are looking for and Run a SELECT on that OBJECT_ID. In the above case, it is 139742. It will bring up all the details for this specific OBJECT_ID.


SELECT *
FROM   CMS_DynamicFields
WHERE  object_id = 139742
Query result example


         139742;"forms.FORMSReportsAnalysisUnit";"Object_ID";"";"2016-02-12 20:31:22"
         139742;"forms.FORMSReportsAnalysisUnit";"active";"true";""
         139742;"forms.FORMSReportsAnalysisUnit";"analyzerProjectName";"KYT4_YCH.rdf_9bc6048e";""
         139742;"forms.FORMSReportsAnalysisUnit";"codeResource";"134264";""
         139742;"forms.FORMSReportsAnalysisUnit";"entry";"uuid:cc538336-c0e8-4ab7-b0e3-f6bbee1cfdb9";""
         139742;"forms.FORMSReportsAnalysisUnit";"execConfigChecksum";"bd1608cb6af54006c5f0a453b42b333b";""
         139742;"forms.FORMSReportsAnalysisUnit";"execDate";"";"2016-02-12 19:43:38"
         139742;"forms.FORMSReportsAnalysisUnit";"execDependencyChecksum";"0";""
         139742;"forms.FORMSReportsAnalysisUnit";"execLog";"C:\Cast_Template\Log\a702d542317d41da9594212273d6e55a\Reports_134266-20160212154332.castlog";""       139742;"forms.FORMSReportsAnalysisUnit";"execStatus";"ExecutionSuccessStatus";""
         139742;"forms.FORMSReportsAnalysisUnit";"execVersion";"dmtid:68ecca09-f36b-494c-8aac-dfbb98e19941";""
         139742;"forms.FORMSReportsAnalysisUnit";"name";"KYT4_YCH.rdf";""
         139742;"forms.FORMSReportsAnalysisUnit";"project";"153643";""
         139742;"forms.FORMSReportsAnalysisUnit";"projectEntry";"dmtid:410f9bb6-57d1-4c6a-abca-174bb93408c2:KYT4_YCH.rdf.rex";"" 
         139742;"forms.FORMSReportsAnalysisUnit";"technology";"140296";""

Query result interpretation

Columns in the result are: OBJECT_ID;ENTITY_GUID;FIELD_GUID;FIELD_VALUE;FIELD_DATE


3) Now run an UPDATE on the field FIELD_VALUE for this specific OBJECT_ID 


UPDATE CMS_DynamicFields
SET    Field_Value = '0'
WHERE  Entity_GUID = 'forms.FORMSReportsAnalysisUnit'
AND    Field_GUID  = 'active'
AND    object_id   = 139742


Query result example
 Result should be success with the number of rows updated


Query result interpretation
 No interpretation required.



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