SQL Resultset preparation in Pentaho Action Sequences ( xactions )

             While we are passing external query to the prpt report or some other purposes we need to modify the sql result set or we need to prepare our own sql resultset by adding some rows or some columns or need to make column wise data into row wise data  to fulfill our business requirement.


So we can prepare sql result set by using javascript step in pentaho xactions.



Add the action new action step "Relational"

      Process Actions > Click on + Button (Add new Action) >> Get Data From >>> Relational

The following is the sample query.

SELECT
    '10' AS AAA_Q1,
    '30' AS AAA_Q2,
    '15' AS AAA_Q3,
    '25' AS AAA_Q4,
    '20' AS BBB_Q1,
    '35' AS BBB_Q2,
    '15' AS BBB_Q3,
    '25' AS BBB_Q4 from dual

This action output value would be a resultset(query_result).

Add the another action, new action step "JavaScript". This is where you can prepare your own resultset.

  Process Actions > Click on + Button (Add new Action) >> Get Data From >>> JavaScript

   1. Use previous action ("Relational") output as input for this action.
   2. At javaScript console, you can use these following methods to get resultset values and to add the value to the result.

      2.1   To get the row count
            query_result.getRowCount();
      2.2  To get the column count
             query_result.getColumnCount();
      2.3   To get the value from the resultset, index values would start from 0.
              query_result.getValueAt( rowIndex, columnIndex);

      
      2.4  To create javascript resultset and assign to one variable.
                      records_result = new JavaScriptResultSet();      

      2.4  To add column header array to newly created result set.
                 2.4.1 column header array creation.
                 labelColumnVals = new Array('AAA','YYY');
         
              records_result.setColumnHeaders(labelColumnVals);

      2.5 To add rows to newly created resultset.
               records_result.addRow(rowArray);
             
      Sample logic to add rows to the newly created reultset.
            
                  numofrows = 2;
                  colCount = 0;
                  var row = new Array();
                
                    for(i=0; i < numofrows ; i++) {           
                           row[0] = query_result.getValueAt( 0, colCount++);
                           row[1] = query_result.getValueAt( 0, colCount++);
                           records_result.addRow(row);
                           }
     
    You can use your own business logic to create resultset.   

You can use this resultset output as Relational action output resultset where you want in your logic or in a query as a subquery.