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_at_ptn-nntp-reader02.plus.net>
>
> Use a union statement.
>
> Drill-down into sub-reports by chaining further
> stored procedures to the upper level report.
>
> Easy to do in SQL.
> 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.
Date: Tue, 07 Jun 2005 16:24:19 +0100
Message-ID: <42a5bc25$0$8721$ed2619ec_at_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.
Paul. Received on Tue Jun 07 2005 - 17:24:19 CEST