Categorizing data from DB2

Question

I am using a jdbcQuery as my datasource for the Extension Library Data View control to pull DB2 data and the page throws an error 500 when I use DB2 column names in the categoryColumn and/or summaryColumn columnName or value properties, but not the extraColumns. I can understand why this might occur for the categoryColumn because the DB2 column isn't categorized, but would have thought the DB2 columns were considered summary columns. Anyway, is there another control I can use or some hack method to make columns appear categorized when the column from the datasource isn't?

Domino 8.5.3FP3 Extension Library 9.0.0.v00_02_20130515-2200 DB2 V10.1 for z/OS

Answer 1

The first repeat should contain the column headers, images to identify categoization, computed category value, and second repeat. The datasource for the second repeat is making a new SQL call to pull in your specific fields based off the categorized value from the first repeat. The field values are concatenated together so that they can be parsed for each of my computed fields. My blog will show more detail with screenshots. Screenshot shows categorized values on my XPage that are explained by code sample.

<xp:panel xp:key="facetMiddle">
 <xp:table>
  <xp:tr>
   <xp:td></xp:td>
  </xp:tr>
  <xp:repeat
   var="collRowData"
   value="#{jdbcQuery1}">  //name of XPage datasource
   <xp:tr>
    <xp:td>
     <xp:panel id="mainpanel">
      <xp:image
       id="image1"
       style="height:16.0px;width:16.0px"
       rendered="#{jav`enter code here`ascript:
        context.getSubmittedValue()=='hide' || 
        context.getSubmittedValue()== null;}">
        <xp:this.url><![CDATA[#{javascript:"/.ibmxspres/domino/icons/vwicn096.gif"}]]></xp:this.url>
         <xp:eventHandler
          event="onclick"
          submit="false">
          <xp:this.script><![CDATA[var visibility = 'show';
           XSP.partialRefreshGet("#{id:mainpanel}", {
           params: {'$$xspsubmitvalue': visibility}, 
           onComplete: function () {
           XSP.partialRefreshGet("#{id:secondpanel}", {
           params: {'$$xspsubmitvalue': visibility}});}});]]></xp:this.script>
         </xp:eventHandler>
      </xp:image>
      <xp:image
       id="image2"
       style="height:16.0px;width:16.0px"
       rendered="#{javascript:context.getSubmittedValue()=='show'}">
        <xp:this.url><![CDATA[#{javascript:"/.ibmxspres/domino/icons/vwicn097.gif"}]]></xp:this.url>
         <xp:eventHandler
          event="onclick"
          submit="false">
           <xp:this.script><![CDATA[var visibility = 'hide';
            XSP.partialRefreshGet("#{id:mainpanel}", {
            params: {'$$xspsubmitvalue': visibility}, 
            onComplete: function () {
            XSP.partialRefreshGet("#{id:secondpanel}", {
            params: {'$$xspsubmitvalue': visibility}});}});]]></xp:this.script>
         </xp:eventHandler>
      </xp:image>
      <xp:text
       escape="true"
       id="computedField2"
       style="margin-left:5.0px;font-weight:bold">
        <xp:this.value><![CDATA[#{javascript:if (collRowData[0]) {
          collRowData[0] }
         else {
          '- not categorized -'}}]]>
        </xp:this.value>
      </xp:text>
     </xp:panel>
     <xp:panel id="secondpanel">
      <xp:repeat
       var="character"
       rendered="#{javascript:context.getSubmittedValue()== 'show'}">
       <xp:this.value>
        <![CDATA[#{javascript:
         var sql = "Select MBR_FIRST_NM, MBR_LAST_NM from TECH.TBSCRA_DEMO where MBR_ACCESS_NBR = '" + collRowData[0] + "'"; //single quote is before and after collRowData[0]
         var rs = @JdbcExecuteQuery("db2",sql);
         var fName;
         var lName;
         while(rs.next()) {
         //Get the data from the row using the column name
         fName = rs.getString("MBR_FIRST_NM");
         lName = rs.getString("MBR_LAST_NM");
        }
        return fName + "," + lName}]]></xp:this.value>
       <xp:table>
        <xp:tr>
         <xp:td>
          <xp:text
           escape="true"
           id="firstname">
           <xp:this.value><![CDATA[#{javascript:
           var nm = @Explode(character,",");
            if (character) {
             nm[0] }
            else {
             '- not categorized -'}}]]></xp:this.value>
          </xp:text>
         </xp:td>
        </xp:tr>
       </xp:table>
      </xp:repeat>
     </xp:panel>
    </xp:td>
   </xp:tr>
  </xp:repeat>
 </xp:table>
</xp:panel>