rollup with pl sql

[pando]

hi

does anyone know how to perform group by rollup() with pl sql? It´s because we are using 7.3.4 and cant use this feature

something like

select job, deptno, sum(sal)
from emp
group by rollup(job,deptno)

[pipo]

well, no similar features is available ... you could use breaks and computes, but it's just a SQL*Plus feature, and it won't give you exactly what you want ...
anyway it should be possible to write a rollup-like function in PL/SQL, but it won't be easy to do so :/

[pando]

cant use sqlplus because it´s gonna run from an application

so is it tricky :/

[pipo]

I think you could quite easily build a PL/SQL function doing the job specifically for the tables you'll use, but if you need a generic script that can do the work for any table on any number of columns, then I doubt it will be possible to do :/

really tricky :|

[pando]

hmm how would it be just for a single table :-? for example the emp table

[pipo]

I'm working on it
C U in 10-15 minutes with a solution !

[pando]

i am trying too

[pipo]

set serveroutput on

declare

cursor my_curs is
select job, deptno, sum(sal) S
from emp
group by job, deptno;

tmp_sum number := 0;
tmp_job varchar2(9) := '';
global_sum number := 0;

begin
dbms_output.enable(100000);
for var_curs in my_curs
loop
if (var_curs.job != tmp_job and tmp_job IS NOT NULL) then
dbms_output.put_line(rpad(tmp_job,15,' ') || ' ' || tmp_sum);
tmp_job := var_curs.job;
tmp_sum := var_curs.S;
dbms_output.put_line(rpad(tmp_job,11,' ') || ' ' || var_curs.deptno || ' ' || var_curs.S);
global_sum := global_sum + var_curs.S;
else
tmp_job := var_curs.job;
tmp_sum := tmp_sum + var_curs.S;
global_sum := global_sum + var_curs.S;
dbms_output.put_line(rpad(tmp_job,11,' ') || ' ' || var_curs.deptno || ' ' || var_curs.S);
end if;
end loop;
dbms_output.put_line(rpad('total = ',17,' ') || global_sum);
end;
/

[Jani]

Hi
What is purpose of ROLLUP, and Why data shown in repeated format ?

select deptno,job, sum(sal)
from emp
where deptno = 20
group by rollup(job,deptno)
/

DEPTNO JOB SUM(SAL)
---- --------- ---------
20 ANALYST 6000
ANALYST 6000
20 CLERK 1100
CLERK 1100
20 CLERK. 800
CLERK. 800
20 MANAGER 2975
MANAGER 2975
20 S D 6500
S D 6500
20 s e 10000
s e 10000

27375


Thanx

[pipo]

rollup makes partial sums, it's very useful, but you rexample isn't good since you group by job, deptno, with deptno fixed to a value ... just do :

select deptno,job, sum(sal)
from emp
group by rollup(job,deptno)


and you'll see the usefulness of rollup