[Openroad-users] QueryObject and Crosstabs

[Bodo Bergmann]

Kim,
 
here are two simple examples (both using Array mode).
 
1. QueryObject example:
 
This retrieves data from table v_customer into tablefield customer_tf
having same columns as tha table.
Click on save_btn stores all changes to the tablefield according to the
_rowstate of each row
(new, changed or marked as deleted using SetRowDeleted) back into the
table.
  
initialize() = 
declare 
    myrow = integer not null; 
    i = integer not null; 
    qo = QueryObject; 
endeclare 
begin 
    /* Provide attributes to build Select- and From-Clauses */ 
    qo.Tables[1].TableName = 'v_customer'; 
    qo.Columns[1].ColumnName = 'acctno'; 
    qo.Columns[2].ColumnName = 'cname'; 
    qo.Columns[3].ColumnName = 'cphone'; 
    qo.Columns[4].ColumnName = 'cstatus'; 
    /* Order By Clause */ 
    qo.Columns[2].OrderBy = 1; 
    qo.Columns[2].AsName = 'cname'; 
    /* Where Clause -  change it as you need */ 
    qo.RunTimeWhere.Value = ''; 
    /* Set target variables */ 
    for i = 1 to qo.Columns.LastRow do
        qo.Columns[i].Targets[1].Expression = qo.Columns[i].ColumnName; 
        qo.Columns[i].Targets[1].IsSelectTarget = TRUE; 
        qo.Columns[i].Targets[1].IsUpdateTarget = TRUE; 
        /* all Columns are from v_customer */
        qo.Columns[i].FromTable = qo.Tables[1]; 
    endfor;
    /* Column 1 is the key column - should not be changed */
    qo.Columns[1].Targets[1].IsUpdateTarget = FALSE; 
    qo.Columns[1].Targets[1].IsUpdateWhere = TRUE; 
    qo.Columns[1].Targets[1].IsDeleteWhere = TRUE; 
    qo.TargetArray = customer_tf; 
    qo.TargetPrefix = 'customer_tf[myrow].'; 
    /* Retrieve data */ 
    qo.Open(querymode = QY_ARRAY, scope = CurFrame.Scope); 
    FIELD(customer_tf).UpdField(); 
    ss.Close(); 
    COMMIT;
end;
 
on click save_btn = 
begin 
  for myrow = customer_tf.FirstRow to customer_tf.LastRow do
    if customer_tf[myrow]._Rowstate = RS_NEW then 
        qo.DBInsert(); 
        customer_tf[myrow]._Rowstate = RS_UNCHANGED; 
    elseif customer_tf[myrow]._Rowstate = RS_DELETED then 
        qo.DBDelete(); 
        customer_tf.RemoveRow(rownumber = myrow); 
    elseif customer_tf[myrow]._Rowstate = RS_CHANGED then 
        qo.DBUpdate(); 
        customer_tf[myrow]._Rowstate = RS_UNCHANGED; 
    endif;
  endfor; 
  COMMIT;
end;
 
2. CrossTable example:
 
Given a data table "store" with columns "product", "color"  and "amount"
sample contents:
 
product    color    amount
abc        blue       1
abc        red        2
xyz        yellow    10
xyz        blue       5
 
Now you want a result the entries of the "color" column
will be column header for your result table field:
 
product    blue    red    yellow
abc         1        2     null
xyz         5      null    10
 
This can be accomplished by using a ColumnCross
and a TableField "tf" having the columns "product", "e1", "e2", "e3"
(or you create the columns dynamically):
 
initialize()= 
declare 
     cc = ColumnCross; 
     i = integer not null; 
begin 
    cc.scope = CurFrame.Scope; 
    cc.TargetArray = tf; 
    cc.Tables[1].TableName = 'store'; 
    cc.Columns[1].ColumnName = 'product'; 
    cc.CrossColumn.ColumnName = 'color'; 
    cc.Aggregate.ColumnName = 'amount'; 
  
    cc.Columns[1].Targets[1].Expression = 'product'; 
    cc.Columns[1].Targets[1].IsSelectTarget=TRUE; 
    cc.SetCols(); 
    for i=1 to cc.AcrossValues.LastRow do 
        cc.AcrossValues[i].Targets[1].Expression = 'e'+varchar(i); 
        ColumnField(FormField(CurFrame.TopForm.FieldByFullName( 
            fullname = 'tf[*].e'+varchar(i))).ParentField).title =
            cc.AcrossValues[i].AsName; 
        cc.AcrossValues[i].Targets[1].IsSelectTarget=TRUE; 
    endfor; 
    cc.Open(querymode=QY_ARRAY); 
    cc.Close(); 
    COMMIT; 
end
 
Hope this helps,
Bodo.
 
Bodo Bergmann
Senior Software Engineer
OpenROAD Worldwide Development
Ingres Corp.
 

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Kim Ginnerup
Sent: Sunday, March 02, 2008 9:57 AM
To: openroad-users at peerlessit.com
Subject: [Openroad-users] QueryObject and Crosstabs



Hi there,

 

Have any of you a "simple" example on how to use queryobjects and
crosstabs.

I would like to let user dynamically specify pivot like queries on data.

I know the table in advance so I know all available columns, but I do
not know how they will be grouped or aggregated.

 

I have read the manual on this subject several times over the years.
Somehow I have mental blockade when I reach the end. ;-)

 

 

Kim Ginnerup

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080302/9ee7790a/attachment.html