Re: Rowset calculations in PL/SQL

On Jan 26, 12:46 am, Sven Witting <newsgr..._at_sven-witting.de> wrote:
> Hi,
>
> I would like to know if (and how) the following problem can be solved in
> PL/SQL (using Oracle 9i and above):
>
> For a data warehouse we need the following functionality: Depending on a
> drilldown-dimension (previously selected by the user) a SQL-statement
> has to be generated dynamically. For the resultset, a summary row has to
> be computed; both has to be available as an OUT Ref-Cursor of some
> function. This function could for example have the following signature:
>
> function drilldown(p_dimension varchar2,
> p_result out sys_refcursor,
> p_summaryrow out sys_refcursor) return number
>
> The function has to be able to be called from different frontends like
> NET-Clients or JAVA-Webapplications.
>
> The following example shows how a resultset can
> principally look like:
>
> division employees sales
> ---------------------------------------
> division a 20 100,000.00
> division b 15 70,000.00
> division c 8 30,000.00
>
> The computation of each column-summary (count, sum, avg, min, ...) is
> given by a metadata repository and depends on the column name or the
> column alias respectively.
>
> The summary row might be computed as count, sum, sum:
> 3 43 200,000.00
>
> Is this somehow possible in PL/SQL?
>
> Because of the possibly long runtimes of the queries,
> I have to avoid in any case that the statement runs twice.
>
> Thus the following solution is not desirable:
>
> [...]
>
> -- dynamically generated SQL-Statement
> l_query := 'SELECT division, employees, sales FROM table';
> OPEN p_result
> FOR l_query;
>
> -- dynamically generated SQL-Statement for the summary row
> l_query_sum := 'SELECT COUNT(*), SUM(employees), SUM(sales)'
> ||' FROM table';
> OPEN p_summaryrow
> FOR l_query_sum;
>
> [...]
>
> Can anybody help?
>
> Thank you in advance,
> Sven

Hmm... You want two result sets in two cursors, but you don't want two queries to be executed. Looks like your requirements contradict each other... Can you get away with single cursor that will return summary row as part of the result set (the last row?) If so, look up ROLLUP and CUBE extensions of the GROUP BY clause in the docs. For your example, the query could be something like this:

select division, count(division), sum(employees), sum(sales) from table group by rollup(division);

The last row will have DIVISION column NULL and all others summarized. Not exactly what you want but pretty close, so might be worth experimenting with.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Fri Jan 26 2007 - 00:45:47 CST