Perform functions on recordset?

[Mika]

Is it possible to perform functions on a recordset (rather than on the
source database), e.g. COUNT, MAX etc

The reason I would like to do this is because I have a hugely complex view
which produces results with over 60 columns of data. I would like to display
all data but also highlight individual "cells" in the table which have the
highest (i.e. MAX) data for each column. I would also like totals at the
bottom of each column.

Obviously I could get the MAX value for each column by performing it on the
SQL data but that would effectively mean running the query twice and would
take twice as long.

Any suggestions?

 

[Bob Barrows [MVP]]

Is it possible to perform functions on a recordset (rather than on the
source database), e.g. COUNT, MAX etc

Not without time-and-resource-consuming looping.

The reason I would like to do this is because I have a hugely complex
view which produces results with over 60 columns of data. I would
like to display all data but also highlight individual "cells" in the
table which have the highest (i.e. MAX) data for each column. I would
also like totals at the bottom of each column.

Obviously I could get the MAX value for each column by performing it
on the SQL data but that would effectively mean running the query
twice and would take twice as long.

What database are you using? Does "SQL data" imply that you are using SQL
Server? if so, what version? T-SQL provides CUBE and ROLLUP functionality
which may provide what you are looking for. If you are using SQL Server, you
should look up these terms in Books Online (BOL).

You should test this, but I submit that "running the query twice" would
outperform retrieving the data and looping through it to perform your
aggregations.

You can use subqueries to achieve most of your objectives in a single query.

Bob Barrows

 

[Adrian Moore]

Mika,

You might find the QueryRecordsets component I've been working on useful.
Its allows you to issue SQL queries against ADO recordsets. COUNT and MAX
are just two of the many builtin functions supported.

It lets you perform complex SQL SELECT statements including UNION, JOINS,
GROUP BY, HAVING, ORDER BY, sub-queries, functions, aggregates against the
ADO recordsets..

This component allows applications like Visual Basic 6.0 to register ADO
recordsets into a DataSet collection, perform complex SQL queries and return
the results as an ADO recordset.

For a trial download or more information, please visit
http://www.queryadataset.com/recordsets.aspx

Thanks
Adrian Moore

Peersoft

 

[William \(Bill\) Vaughn]

This is fairly simple with Reporting Services.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________