addColumn (QueryResultsProcessor - Java™)

Creates a single column of values to be returned when QueryResultsProcessor is executed.

Column values can be generated from a field or a formula. Sorting order, categorization and hidden attributes determine the returned stream of results entries. Columns span all input result sets and databases taking part in the QueryResultsProcessor. Execute calls in the QueryResultsProcessor require at least one column to be specified.

From Domino 14.0.0 onwards, if you are planning to create a view using .executeToView() and you specify a view whose design to use, then you can call .addColumn() and specify the name of the one of the view’s columns whose properties you want to override. (See executeToView (QueryResultsProcessor - Java) for an example)

Define in

QueryResultsProcessor

Syntax

public void addColumn(String name, String title, String formula, int sortorder, boolean ishidden, boolean 
iscategorized) throws NotesException
public void addColumn(String name) throws NotesException

Parameters

string name

The unique programmatic name of the column within a QueryResults Processor instance. If there is no override using the addFormula method call, the name provided is treated as a field name in each database involved in the QueryResultsProcessor object.

In JSON output, the name value used is the element name for each returned entry. Values in the name field can also specify aggregate functions. Aggregate functions require categorized columns and they return computed numerical values across sets of results within a given category. For more information, see the description of booleaniscategorized. For aggregate functions requiring a name as an argument, that name can be overridden the same as any name value without the aggregate function.

Shortened call parameter values

Using the second call (with a single parameter) is equivalent to the following:

addColumn(name, “”, SORT_UNORDERED, false, false, “”);

Full call parameter values

String title

The display title of the column. Used only in generated views, the title is the UI column header. 

String formula
Formula language string to serve as the default means of computing values for the column. If not supplied and if not overridden by an addFormula value, the name argument is treated as a field name. The precedence of supplying column values is:
  1. AddFormula Formula Language override
  2. Formula argument of the AddColumn method
  3. Use the name argument of the AddColumn method as a database field name
If supplied, the Formula Language provided is applied to the column across all results added using addCollection or addDominoQuery. Formulas are not allowed on columns with aggregates.
Note: Do not enter a literal (for example, "\"My Literal\"") as a formula. Doing so results in malformed views. To return a literal value, use the addFormula method, instead.
int sortorder

A constant to indicate how to sort the column. Values are sorted case and accent insensitively, by default. Legal values are:

SORT_UNORDERED

SORT_ASCENDING

SORT_DESCENDING

Multiple sort columns can have sortorders, and each order specified is sequentially applied in the order of addColumn calls. Field lists (multiply-occurring field values) are compared processed using field occurrences from first to last sequentially. 

boolean ishidden

Specify this to sort by a column value but not return it. If true, the column cannot have a sortorder of SORT_UNORDERED and cannot have an iscategorized value of true. 

boolean iscategorized

Categorized columns have a single entry returned for each unique value with entries containing that value nested under it. In JSON results, these nested entries are represented in arrays under each categorized unique value.

Multiply-occurring fields (i.e. lists) are not allowed to be categorized.

Any categorized column with a preceding uncategorized column or columns will force the creation of categorized values containing all prior uncategorized columns plus the categorized column.

A categorized column creates values for any preceding uncategorized column in addition to the categorized column. Categorized columns can nest to create subcategories. Consider the following columns, added in order left to right:
Col1 Col2 Col3 Col4 Col5 Col6 Col7
categorized categorized categorized
The first category contains a value for Col1. A subcategory contains categorized values for Col2, Col3 and Col4. A third category level contains categorized values for Col5 and Col6. Col7 value is included for each document under each of the three categories.
Aggregate function names begin with double @@ signs and are case insensitive. They cannot be nested one within another. Their numeric output can be either integer or floating point values. Examples of the five aggregate functions are:
  • @@avg(col1) Returns the sum all values of col1 within the current category divided by the number of documents.
  • @@sum(col2) Returns the sum of all values of col2 within the current category.
  • @@max(col3) Returns the maximum numeric value of col3 within the current category.*
  • @@min(col4) Returns the minimum numeric value of col4 within the current category.*
  • @@count() Returns the count of documents within the current category.*
* Not supported for executeToView method.

Aggregate function columns must appear after the categorized column whose values control their computation. They are always SORT_UNORDERED, that is, unsorted.

Consider the following set of columns, added in order left to right:
1 2 3 4 5 6 7 8

Col1

Col2
categorized

@@avg(col3)

@@sum(col4)

Col5

Col6
categorized

@@avg(col7)

Col8
Col1 and Col2 are combined to create categorized values. The average of col3 values and the sum of col4 values are produced for each combined value. A subcategory containing the combination of Col5 and Col6 values creates values under the first category and the average of col7 values in documents containing each subcategory value is produced. Finally, Col8 values are produced for each document under both the category and subcategory. Note that you must use successive columns to combine columns to produce categories, so column 4 cannot switch places with column 5 nor can column 8 switch places with 7. The required column ordering then, is:
  • a. Any non-categorized, non-aggregate columns
  • b. A single categorized column
  • c. Any aggregate columns (can be multiple as above)
  • d. Finally, any document-level columns not taking part in categorization. That is, document detail.
Violations of that ordering result in validation errors or unexpected results. See the examples for the expected output using categories and aggregate functions.

Example

The following example creates three sort columns (ascending, descending and unordered) with field values:
QueryResultsProcessor qrp = db.createDominoQuery();
…
qrp.addColumn("sales_person", "",””,
     QueryResultsProcessor.SORT_ASCENDING, false, true); // categorized also
qrp.addColumn("ordno, "", “”,
     QueryResultsProcessor.SORT_DESCENDING, false, false, “order_number + 1000”);
qrp.addColumn("order_origin", "", “”, 
     QueryResultsProcessor.SORT_UNORDERED, false, false);

Example with categories and aggregate functions

The following example creates nested category results with the column names shown in the previous table. For an example using overrides, see case 2 in the addFormula example.
// 1st category - 2 fields making 1 categorized key value	
qrp.addColumn("Department", "Dept", , “@left(dept; 10)”, 
     QueryResultsProcessor.SORT_ASCENDING, 
     false, false);
qrp.addColumn("JobTitle", "Job Title ", “”, QueryResultsProcessor.SORT_ASCENDING, 
     false, true);
				
// aggregates off the first category
qrp.addColumn("@@avg(salary)", "", “”, QueryResultsProcessor.SORT_UNORDERED, 
     false, false);
qrp.addColumn("@@sum(salary)", "", “”, QueryResultsProcessor.SORT_UNORDERED, 
     false, false);
				
// 2nd category -  2 field categorized key
qrp.addColumn("GEO”);  // see shortened method form below
qrp.addColumn("OU", "Organizational Unit ", “”, QueryResultsProcessor.SORT_ASCENDING,
     false, true);
				
// 4 aggregates off the 2nd category
qrp.addColumn("@@count()", "", “”, QueryResultsProcessor.SORT_UNORDERED, false, false);
qrp.addColumn("@@avg(salary)", "", “”, QueryResultsProcessor.SORT_UNORDERED, 
     false, false);
qrp.addColumn("@@sum(salwithraise)", "", “”, “”, QueryResultsProcessor.SORT_UNORDERED, 
     false, false);
qrp.addColumn("@@avg(salwithraise)", "", “”, QueryResultsProcessor.SORT_UNORDERED, 
     false, false);
			
// document detail under both categories - unique to Domino
qrp.addColumn("FullName", "Full Name ", “”, QueryResultsProcessor.SORT_ASCENDING, 
     false, false);

Example: Shortened call parameter values

The following example creates three sort columns (ascending, descending and unordered) with field values:
QueryResultsProcessor qrp = db.createQueryResultsProcessor ();
…
qrp.addColumn("sales_person", "", “”,
     QueryResultsProcessor.SORT_ASCENDING, false, true); // categorized also
qrp.addColumn("ordno_adjusted, "", “”,
     QueryResultsProcessor.SORT_DESCENDING, false, false);
qrp.addColumn("order_origin"); // to use simplified syntax