A compute result set is generated by the execution of a Transact-SQL select statement that contains a compute clause. A compute clause generates a compute result set every time the value of its bylist changes. A compute result set consists of a single row containing a number of columns equal to the number of row aggregates in the compute clause.
For example, consider the query:
select type, price from titles
where price > $12 and type like "%cook"
order by type, price compute sum(price) by type
The query returns regular rows (with columns type and price). Intermixed with the regular rows, the query returns compute result sets each time the value of type changes in the regular row results. Each compute result set contains a single row with one column for the sum(price) expression.
See the Adaptive Server Enterprise Reference Manual for more examples of queries with a compute clause.
In addition to ct_res_info, ct_describe, ct_bind, and ct_fetch, an application can call ct_compute_info while processing compute row results. ct_compute_info provides a variety of compute row information. The information available from ct_compute_info includes:
The compute ID for a compute row
A query can have more than one compute clause. ct_compute_info(CS_COMP_ID) retrieves the number of the compute clause that generated a compute result set. A compute row ID of 1 corresponds to the first compute clause in the query.
The compute bylist
The compute bylist is the list of columns that follows the by keyword in the compute clause. In the application, the bylist is represented by an array of CS_SMALLINT values, each of which represents the position of a column in the select list. For example:
select dept, name, year, sales from employee
order by dept, name, year
compute count(name) by dept, name
If you execute this query, then the bylist values are 1 and 2, corresponding to the positions of dept and name in the select list.
ct_compute_info(CS_BYLIST_LEN) returns the length of the bylist, and ct_compute_info(CS_BYLIST) populates an application-allocated array with the bylist column numbers.
Compute row select-list column IDs
Select-list column IDs are available for each column in a compute row. The select-list column ID is the select-list position of the column from which the compute-row column was derived. For example, this query returns compute rows containing one column for the sum(price) expression:
select type, price from titles
where price > $12 and type like "%cook"
order by type, price compute sum(price) by type
The corresponding select-list column ID is 2, which is the position of the price column in the select list.
ct_compute_info retrieves compute column IDs when called with type as CS_COMP_COLID and colnum as the compute column number.
Compute column operators
ct_compute_info, when called with type as CS_COMP_OP and colnum as the compute column number, retrieves a symbolic constant that indicates the operator with which the column value was computed. See the ct_compute_info reference page in the Open Client Client-Library/C Reference Manual for a list of these operators.
Most synchronous applications use a program structure similar to the following one to process a compute result set:
case CS_COMPUTE_RESULT
(optional)ct_compute_info to get bylist length,
bylist, or compute row id
ct_res_info(CS_NUMDATA) to get the number of columns
for each column:
ct_describe to get a description of the column
ct_bind to bind the column to a program variable
(optional: ct_compute_info to get the compute
column id or the aggregate operator for the
compute column)
end for
while ct_fetch returns CS_SUCCEED or CS_ROW_FAIL
if CS_SUCCEED
process the compute row
else if CS_ROW_FAIL
handle the failure
end if
end while
switch on ct_fetch’s final return code
case CS_END_DATA...
case CS_CANCELED...
case CS_FAIL...
end switch
end case
