Create a view with a calculated column to be used in Clarify CRM and Dovetail Agent.

2007-12-20T20:27:12Z

Our customer would like to query all the cases including a column with a calculated number of "open" subcases related to each case record. They want to query for all of the cases that have any open subcases. A normal view would not work because it cannot calculate the count of related object with specified condition.

Steps taken.

1. We showed it could be done in SQL (See SQL below). Note the nested SQL statement to calculate the number of open subcases.

select c.objid, c.id_number, cond.title, cond.title,
  (SELECT count(*)
   FROM table_subcase sc
   INNER JOIN table_condition sc_cond ON sc_cond.objid = sc.subc_state2condition
   WHERE sc.subcase2case = c.objid AND sc_cond.title LIKE 'OPEN%') 
      as open_subcase_count 
FROM table_case c
INNER JOIN table_condition cond ON cond.objid = c.case_state2condition

2. Created the following SchemaEditor SchemaScript.

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">

  <addSqlView name="cases_with_open_subcases_count" id="4081">
    <description>Case identifiers and condition and the count open subcases for the case</description>
    <sql>select c.objid, c.id_number, cond.title, cond.s_title,
(SELECT count(*)
FROM table_subcase sc
INNER JOIN table_condition sc_cond ON sc_cond.objid = sc.subc_state2condition
WHERE sc.subcase2case = c.objid AND sc_cond.s_title LIKE 'OPEN%') as open_subcase_count 
FROM table_case c
INNER JOIN table_condition cond ON cond.objid = c.case_state2condition
</sql>
  </addSqlView> 

  <addSqlViewColumn name="case_objid" sqlView="cases_with_open_subcases_count" dataType="Integer" />
  
  <addSqlViewColumn name="case_id_number" sqlView="cases_with_open_subcases_count" dataType="String">
    <length>255</length>
    <isSearchable>false</isSearchable>
  </addSqlViewColumn>

  <addSqlViewColumn name="case_cond_title" sqlView="cases_with_open_subcases_count" dataType="String" >
    <length>80</length>
    <isSearchable>true</isSearchable>
  </addSqlViewColumn>
  
  <addSqlViewColumn name="s_case_cond_title" sqlView="cases_with_open_subcases_count" dataType="String" >
    <length>80</length>
  </addSqlViewColumn>
    
  <addSqlViewColumn name="open_subcase_count" sqlView="cases_with_open_subcases_count" dataType="Integer" />
  
</schemaScript>

3. Previewed and applied the schema changes against the Amdocs/Clarify database using the Dovetail SchemaEditor.

Preview schema changes: SchemaEditor.exe -p
Apply schema changes: SchemaEditor.exe -a

4. Here is the code that using the new view within the Dovetail Agent application.

<body>
<table>
  <tr>
    <td Case Id </td>
    <td Case Condition </td>
    <td Open Subcases </td>
  </tr>
<%
  var caseObjid = Request.QueryString('case_objid') + '';
  var boCase = FCSession.CreateGeneric('cases_with_open_subcase_count');
  boCase.AppendFilter('open_subcase_count', '>', 0);
  boCase.Query();
    
  while(!boCase.EOF) {
  %>
         <td > <% =boCase('case_id_number') + "" %>           </td>
         <td > <% =boCase('case_cond_title') + "" %>             </td>
         <td > <% =boCase('open_subcase_count') + "" %>  </td>
         </tr>
<%
         boCase.MoveNext();
  }
%>

5. Here are the results. The Dovetail Agent with a "Testing" tab - the grid on the tab is filled with the all of the cases that have open subcases.

2 cases are displayed in the grid.
- Case 48 has 1 open subcase.
- Case 53 has 3 open subcases.
This solution works against both MSSQL and Oracle databases.

6. Here are a few links for more information about the Dovetail SchemaEditor

Dovetail SchemaEditor documentation: http://www.dovetailsoftware.com/resources/docs.aspx?product=SchemaEditor
Dovetail SchemaEditor product: http://www.dovetailsoftware.com/solutions/administration.aspx
Dovetail SchemaEditor customization example: http://blogs.dovetailsoftware.com/blogs/gsherman/archive/2007/03/23/schema-changes-are-a-breeze-with-schemascript.aspx

***Please note that I paired with Kevin Miller (http://blogs.dovetailsoftware.com/blogs/kmiller/default.aspx) to come up with this solution.

· We hope that you find this information useful!

Dan Bergondy

Create a view with a calculated column to be used in Clarify CRM and Dovetail Agent.