OraQA

When you use an ORDER BY clause in your SQL SELECT command, rows with the same value in the ordered column (or expression) are displayed together in your output.

The purpose of these scripts is to provide a solution for:

a. Suppressing Duplicate Values
b. Computing Summary Lines

Install scripts:

create table EMP_SALARY
(ID number(10) primary key,
DEP_ID number(10),
SAL_DATE DATE,
ENAME VARCHAR2(10),
SALARY NUMBER)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(1, 10, TO_DATE('10/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Claudiu', 1000)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(2, 10, TO_DATE('10/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Monica', 900)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(3, 10, TO_DATE('10/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Bogdan', 1100)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(4, 20, TO_DATE('10/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Daniel', 1300)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(5, 20, TO_DATE('10/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Silviu', 1500)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(6, 10, TO_DATE('11/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Claudiu', 1050)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(7, 10, TO_DATE('11/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Monica', 910)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(8, 10, TO_DATE('11/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Bogdan', 1000)
/
Insert into EMP_SALARY
(ID, DEP_ID, SAL_DATE, ENAME, SALARY)
Values
(9, 20, TO_DATE('11/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'Daniel', 1200)
/
Commit
/

Case scenario 1 - using SQL*PLUS:

set pagesize 200
set linesize 200

break on DEP_ID skip 0 on sal_month skip 1 on report

compute sum label 'sum of dep' of salary on dep_id
compute sum label 'sum of month' of salary on sal_month
compute sum label 'sum total' of salary on report

Select
  DEP_ID, to_char(SAL_DATE,'Month') sal_month,
  ENAME, SALARY
From EMP_SALARY E
order by 1,2,3,4
/

clear computes
clear breaks

Case scenario 2 - using only SQL:

column total_type format A30
column sal_month format A20
column ename format A20

Select
    (
    case
      when ename is null
      and to_char(SAL_DATE,'Month') is not null
      then 'sum of month ('||to_char(SAL_DATE,'Month')||')'
      when to_char(SAL_DATE,'Month') is null
      and dep_id is not null
      then 'sum of dep ('||dep_id||')'
      when dep_id is null
      then 'sum total'
    end
    ) total_type,
    decode(lag(dep_id) over
      (partition by dep_id order by dep_id),null,dep_id,null) dep_id,
    decode(lag(to_char(SAL_DATE,'Month')) over
      (partition by dep_id, to_char(SAL_DATE,'Month') order by
        dep_id,to_char(SAL_DATE,'Month')),null,
        to_char(SAL_DATE,'Month'),null) sal_month,
    ENAME,
    sum(SALARY) salary
  From EMP_SALARY E
  group by rollup (dep_id, to_char(SAL_DATE,'Month'),ename)
/

Case scenario 3 - using pipelined function:

create or replace type EmpResult as object
( total_type varchar2(50),
DEP_ID number(10),
SAL_MONTH varchar2(15),
ENAME varchar2(10),
SALARY number );
/
create or replace type EmpResultTab as table of EmpResult;
/
create or replace function GetEmps
return EmpResultTab pipelined
as

currec EmpResult := EmpResult(null, null,null,null,null);
total EmpResult := EmpResult(null, null,null,null,null);
v_dep_id_old EMP_SALARY.DEP_ID%type;
v_month_old varchar2(15);
v_total_month number:=0;
v_total_depid number:=0;
v_total number:=0;
v_dep_first number:=1;
v_month_first number:=1;

begin

for x in (select
  DEP_ID, to_char(SAL_DATE,'Month') sal_month,
  ENAME, SALARY
from EMP_SALARY E
order by 1,2,3,4) loop

   if x.DEP_ID > v_dep_id_old or
      v_dep_id_old is null then

      if v_dep_first=0 then
         total.total_type:='sum of month ('||v_month_old||')';
         total.DEP_ID:=null;
         total.SAL_MONTH:=null;
         total.ENAME:=null;
         total.SALARY:=v_total_month;
         pipe row(total);

         total.total_type:='sum of dep ('||v_dep_id_old||')';
         total.DEP_ID:=null;
         total.SAL_MONTH:=null;
         total.ENAME:=null;
         total.SALARY:=v_total_depid;
         pipe row(total);

         v_month_old:=null;

         v_month_first:=1;
      end if;

      currec.DEP_ID:=x.DEP_ID;
      v_dep_id_old:=x.DEP_ID;
      v_total_depid:=0;
      v_total_depid:=v_total_depid+x.salary;

   else
      v_total_depid:=v_total_depid+x.salary;
      currec.DEP_ID:=null;
      v_dep_id_old:=x.DEP_ID;
   end if;

   if x.sal_month > v_month_old or
      v_month_old is null then

      if v_month_first=0 then
         total.total_type:='sum of month ('||v_month_old||')';
         total.DEP_ID:=null;
         total.SAL_MONTH:=null;
         total.ENAME:=null;
         total.SALARY:=v_total_month;
         pipe row(total);
      end if;

      currec.sal_month:=x.sal_month;
      v_month_old:=x.sal_month;
      v_total_month:=0;
      v_total_month:=v_total_month+x.salary;

   else
      v_total_month:=v_total_month+x.salary;
      currec.sal_month:=null;
      v_month_old:=x.sal_month;
   end if;

   currec.ename:=x.ename;
   currec.salary:=x.salary;

   v_dep_first:=0;
   v_month_first:=0;
   v_total:=v_total+x.salary;

   pipe row(currec);

end loop;

total.total_type:='sum of month ('||v_month_old||')';
total.DEP_ID:=null;
total.SAL_MONTH:=null;
total.ENAME:=null;
total.SALARY:=v_total_month;
pipe row(total);

total.total_type:='sum of dep ('||v_dep_id_old||')';
total.DEP_ID:=null;
total.SAL_MONTH:=null;
total.ENAME:=null;
total.SALARY:=v_total_depid;
pipe row(total);

total.total_type:='sum total';
total.DEP_ID:=null;
total.SAL_MONTH:=null;
total.ENAME:=null;
total.SALARY:=v_total;
pipe row(total);

return;   

end GetEmps;
/
select * from table(GetEmps)
/

Filed under PL/SQL, SQL. Follow any responses to this entry through this RSS 2.0 feed.