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

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

Applicable RDBMS

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