Re: theory and practice: ying and yang

From: Paul <paul_at_test.com>
Date: Tue, 07 Jun 2005 16:24:19 +0100
Message-ID: <42a5bc25$0$8721$ed2619ec@ptn-nntp-reader02.plus.net>

mountain man wrote:

>>What if a report with subtotals at various levels is required?

>
> Use a union statement.

Sounds possible but a bit painful to do - a case of everything looking like a nail when all you have is a hammer.

>>Or other
>>fancy stuff like running totals, subreports etc?

>
> Drill-down into sub-reports by chaining further
> stored procedures to the upper level report.

Commonly you may want to view subreports at the same time as the main report though.

> Running totals can be written in temp tables
> if the coding is too intensive in one step.

Running totals are possible in SQL but it would make for fairly ugly code, and a client reporting tool could do it much better.

>> Or crosstab reports?

>
> Easy to do in SQL.

Very awkward to do, I'd say. SQL returns results with a fixed number of columns. To get a cross-tab style report where the columns aren't known in advance is impossible with standard SQL - you'd have to use "dynamic SQL" or some other kludge.

>> This kind of thing, which is basically presentation rather than 
>> data extraction is better done by a client tool, is it not?

> It is more efficient to allow all processing of data
> to occur within the (R)DBMS, IMO. There is no
> reason to use a client tool if SQL can do it.

A DBMS is very good at extracting data in a fixed-column tabular form, but this is all about presenting that data in formats that don't fit that template. I'd argue that that job is much better done by a client tool.

Paul. Received on Tue Jun 07 2005 - 10:24:19 CDT