Thanks for the question, Jan.

Asked: July 12, 2000 - 3:05 am UTC

Last updated: April 22, 2013 - 8:14 pm UTC

Version: Server 8.1..5

Viewed 1000+ times

You Asked

Hi Tom
First, thank you very much for your solution to my previous question which was headed 'TABLE types in Oracle PL/SQL. It worked beautifully.

This time I have a question about the new ROLLUP feature of the GROUP BY clause. Is there any way to assign alias names to the totals produced by the ROLLUP? Basicaly we want to be able to pick up the grand totals passed back by a procedure and display them on a Delphi form.

Currently our stored procedure produces a list of accounts, along with their corresponding budget figure, actual amount and the variance. This works fine. But the programmer now wants me to pass back the grand total for each as well. It would appear that this could be achieved by adding the ROLLUP to the GROUP BY clause, so that the grand totals are passed back using the same cursor. However, the programmer uses the alias names to refer to each item, how will the programmer identify the grand totals?

Please find below a copy of the procedure:

procedure LIST_ACTUAL_BUDGET(
p_value in number,
list_cur in out cur_type) is
begin
open list_cur for select
aa.account_id ACC_ACCOUNT_ID,
aa.ACCOUNT_CODE ACC_ACCOUNT_CODE,
aa.DESCRIPTION ACC_DESCRIPTION,
vb.LOCK_VN GLB_LOCK_VN,
sum(decode(gross_amount,null,0,gross_amount)) ACC_ACTUAL,
sum(decode(budget_amount,null,0,budget_amount)) ACC_BUDGET,
sum(decode(gross_amount,null,0,gross_amount)) - sum(decode(budget_amount,null,0,budget_amount)) ACC_VARIANCE
from account aa,
v_budget vb,
v_actuals va
where aa.account_id = vb.account_id (+)
and aa.account_id = va.account_id (+)
and aa.CHART_ID in
(select chart_id
from chart_of_accounts
where entity_id = p_value)
group by aa.account_id,
aa.ACCOUNT_CODE,
aa.DESCRIPTION,
vb.LOCK_VN
order by aa.account_id;
end LIST_ACTUAL_BUDGET;

**PLEASE NOTE: v_budget and v_actuals are views, whereas chart_of_accounts and account are tables

Any suggestion/s you may have would be most welcome. Thanks in advance.

Regards

Jan

and Tom said...

I believe what you are looking for is the "grouping" function. It will set a 0/1 flag to allow you to detect when a row is the result of a ROLLUP or CUBE operation. You would have to add a column or 2 (or more) to your query to retrieve these values for inspection by the client application. Here is an example I set up and then in anticipation of your next question (hey -- why doesn't rollup work in PLSQL), I'll offer up how to let rollup work in plsql (see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:186812348071 <code>for why it doesn't work)

ask_tom@OSI1.WORLD> variable P_SAL number
ask_tom@OSI1.WORLD> exec :P_SAL := 1000

PL/SQL procedure successfully completed.

ask_tom@OSI1.WORLD> select deptno, job, sum(sal),
2 decode(grouping(job),1,1,NULL) job_rollup,
3 decode(grouping(deptno),1,1,NULL) deptno_rollup
4 from emp
5 where sal > :P_SAL
6 group by rollup(deptno,job)
7 /

DEPTNO JOB SUM(SAL) JOB_ROLLUP DEPTNO_ROLLUP
---------- --------- ---------- ---------- -------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 1
20 ANALYST 6000
20 CLERK 1100
20 MANAGER 2975
20 10075 1
30 MANAGER 2850
30 SALESMAN 5600
30 8450 1
27275 1 1

12 rows selected.

Notice how the grouping() function returns a 1 when a row represents a rollup of that column. I've used decode to return 1 or NULL (would normally return 0 or 1 -- the zeroes made the output hard to read). Using this fact, it becomes trivial to find the rollup rows. You'll find though that when you goto put this into a procedure:

ask_tom@OSI1.WORLD> variable x refcursor
ask_tom@OSI1.WORLD> set autoprint on

ask_tom@OSI1.WORLD> begin
2 open :x for
3 select deptno, job, sum(sal),
4 decode(grouping(job),1,1,NULL) job_rollup,
5 decode(grouping(deptno),1,1,NULL) deptno_rollup
6 from emp
7 where sal > :P_SAL
8 group by rollup(deptno,job);
9 end;
10 /
begin
*
ERROR at line 1:
ORA-06550: line 8, column 11:
PLS-00201: identifier 'ROLLUP' must be declared
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored

It fails. We currently must use dynamic sql to execute this type of query. It would look like this:

ask_tom@OSI1.WORLD> begin
2 open :x for
3 'select deptno, job, sum(sal),
4 decode(grouping(job),1,1,NULL) job_rollup,
5 decode(grouping(deptno),1,1,NULL) deptno_rollup
6 from emp
7 where sal > :BV1
8 group by rollup(deptno,job)' using :P_SAL;
9 end;
10 /

PL/SQL procedure successfully completed.

DEPTNO JOB SUM(SAL) JOB_ROLLUP DEPTNO_ROLLUP
---------- --------- ---------- ---------- -------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 1
20 ANALYST 6000
20 CLERK 1100
20 MANAGER 2975
20 10075 1
30 MANAGER 2850
30 SALESMAN 5600
30 8450 1
27275 1 1

12 rows selected.

ask_tom@OSI1.WORLD>

Notice we can still support bind variables and all using the USING clause. This is as efficient as a "static" ref cursor.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

quotation mark and difference between static sql and dynamic sql

A reader, January 13, 2002 - 11:34 am UTC

It fails. We currently must use dynamic sql to execute this
type of query. It would look like this:

ask_tom@OSI1.WORLD> begin
2 open :x for
3 'select deptno, job, sum(sal),
4 decode(grouping(job),1,1,NULL) job_rollup,
5 decode(grouping(deptno),1,1,NULL) deptno_rollup
6 from emp
7 where sal > :BV1
8 group by rollup(deptno,job)' using :P_SAL;
9 end;
10 /

PL/SQL procedure successfully completed.

Tom

The difference between the above code where you used dynamic sql, and when you failed with the same code , is the you are using quotation around the sql statement.

How is the use of quotations making the sql dynamic sql.

Thank you

January 13, 2002 - 1:04 pm UTC

the query is in a string, the query is not known to PLSQL until runtime. The above is the same as:

declare
some_plsql_variable varchar2(512) := 'select deptno .... group by rollup(...';
begin
open :x for some_plsql_variable using :p_sal;
end;

since some plsql variable can contain ANY value.... it is dynamic sql. the plsql compiler doesn't "see" that query at compile time, its hidden in the string.

Interesting -- Generating Dynamic Column names ??

Reader, October 12, 2005 - 3:16 pm UTC

I have table which contains monthly Purchase Order totals.
Users need a report displaying the monthly totals and running totals for diff years. The report will be run based on the user input ( the years for which they want these figures).
The report format is something like

2002 Monthly 2002 Running Cumilative 2003 Monthly 2003 Running Cumilative .....

Jan 2 2
Feb 3 5
March 2 7
April 8 15
.
.
.
.
My question is how to create these dynamic column names ?

Thanks

October 13, 2005 - 9:55 am UTC

with dynamic sql - that is the only way to get dynamic column names...

You'd have to run a query to figure out what the names would be.

Then use that information to build a query that has those names.

Dynamic Column Names

Reader, October 13, 2005 - 10:45 am UTC

Thanks Tom.
Could you pls illustrate with an example. That will make it clearer to me.I will see how I can apply it to my situation.

Thanks

October 13, 2005 - 1:10 pm UTC

a) run query to get column names, or generate them using whatever logic you need to use to generate them.

b) write procedural code that creates a query in a string that select's data you want and uses the strings from (a) to name the columns.

difference

Richard, April 20, 2013 - 6:59 pm UTC

What is the difference between the static ref cursor and dynamic ref cursor?

April 22, 2013 - 8:14 pm UTC

basically, one is known at compile time and one is not.

It is the difference between STATIC SQL in plsql and DYNAMIC SQL - the ref cursor bit doesn't affect that.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14039880210501

static sql:

o you get the dependency tracking, you know what procedures use what tables
o you get auto-binding, you don't even have to think about binding, it just happens
o you know your code will run at runtime, the security, syntax, etc are all checked at compile time

dynamic sql:

o none of the above..

Richard, April 24, 2013 - 11:07 pm UTC

Thanks a lot for your response. I am impressed with your clear and simple explanation.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

quotation mark and difference between static sql and dynamic sql

Interesting -- Generating Dynamic Column names ??

Dynamic Column Names

difference

More to Explore

PL/SQL demos

PL/SQL docs

Assigning alias names to the grand totals resulting from Rollup in a Group By

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

quotation mark and difference between static sql and dynamic sql

Interesting -- Generating Dynamic Column names ??

Dynamic Column Names

difference

More to Explore

PL/SQL demos

PL/SQL docs