Home » SQL & PL/SQL » SQL & PL/SQL » group by group
group by group [message #18352] Tue, 29 January 2002 04:54 Go to next message
Rm69
Messages: 39
Registered: January 2002
Member
Procedure Mis_WorkFlowCapital
(v_start_date date,v_end_date date)
AS

BEGIN

Insert into mis_wrkflow_cap (received, prequota,batched,scanned_received,captured,checked,
rules,returned,disburse,active,pre_reject,rejected,capture_date)

Select received.capital, prequota.capital,batched.capital,scanned_received.capital,
captured.capital,checked.capital,
rules.capital,returned.capital,disburse.capital,active.capital
,pre_reject.capital,rejected.capital,sysdate

from (SELECT sum(CAPITAL) capital
from ZW30800P
where SCDATE between v_start_date
and v_end_date)
received,

(SELECT sum(CAPITAL) capital
from ZW30800P
where STATUS = 'PRE'
and PQDATE between v_start_date
and v_end_date)
prequota,

(SELECT sum(CAPITAL) capital
from ZW30800P
where STATUS = 'BAT'
and BADATE between v_start_date
and v_end_date)
batched,



(SELECT sum(CAPITAL) capital
from ZW30800P
where STATUS = 'REJ'
and REJDATE between v_start_date
and v_end_date)
rejected etc;

end;

This is the table structure

q)Trying to insert the branch and group it by branch.The branch field from the zw30800p is called
branch. l tried several methods and its not working.
Please help !!!!

CREATE TABLE MIS_WRKFLOW_CAP (
BRANCH NUMBER (10),
RECEIVED NUMBER (15,2),
PREQUOTA NUMBER (15,2),
BATCHED NUMBER (15,2),
SCANNED_RECEIVED NUMBER (15,2),
CAPTURED NUMBER (15,2),
CHECKED NUMBER (15,2),
RULES NUMBER (15,2),
RETURNED NUMBER (15,2),
DISBURSE NUMBER (15,2),
ACTIVE NUMBER (15,2),
PRE_REJECT NUMBER (15,2),
REJECTED NUMBER (15,2),
CAPTURE_DATE DATE,
CHANGE_DATE DATE)
Re: group by group [message #18353 is a reply to message #18352] Tue, 29 January 2002 05:30 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
can u send the table structure of ZW30800P
Re: group by group [message #18356 is a reply to message #18352] Tue, 29 January 2002 07:54 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
This should give you a model (just add the date logic and the extra selects, etc.):

12:46:09 ==> insert into mybranch (branch, capital, batched, rejected)
12:46:42 2 (select distinct(branch),
12:46:42 3 (select sum(capital) from zw20800p a where z.branch = a.branch) cap,
12:46:42 4 (select sum(capital) from zw20800p b where status = 'BAT' and z.branch = b.branch) bat,
12:46:42 5 (select sum(capital) from zw20800p c where status = 'REJ' and z.branch = c.branch) rej
12:46:42 6 from zw20800p z);

2 rows created.

12:46:43 ==> select * from mybranch;

BRANCH CAPITAL BATCHED REJECTED
-------------------- --------- --------- ---------
10 32000 10000
20 110000 50000

12:46:51 ==> select * from zw20800p;

BRANCH SCDATE PQDATE BADATE CAPITAL STATUS
---------- ---------- ---------- ---------- --------- -------------
10 01/19/2002 01/24/2002 10000 BAT
10 01/09/2002 20000
10 12/30/2001 2000
20 01/29/2002 20000
20 01/09/2002 20000
20 01/09/2002 01/09/2002 01/09/2002 20000
20 01/09/2002 50000 REJ
Re: group by group [message #18358 is a reply to message #18352] Tue, 29 January 2002 08:36 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Here's a PL/SQL alternative

procedure jonsway (i_start IN DATE, i_stop IN DATE) is
l_cap number;
l_bat number;
cursor c1
is
select distinct(branch) branch from zw20800p
;
function lf_received (i_branch in varchar2) return number is
l_sum number;
begin
SELECT sum(CAPITAL) capital
into l_sum
from ZW20800P
where SCDATE between i_start
and i_stop
and branch = i_branch;
return(l_sum);
end lf_received
;
function lf_batched (i_branch in varchar2) return number is
l_sum number;
begin
SELECT sum(CAPITAL) capital
into l_sum
from ZW20800P
where status = 'BAT'
and BADATE between i_start
and i_stop
and branch = i_branch;
return(l_sum);
end lf_batched
;
begin
for crec in c1
loop
l_cap := lf_received(crec.branch);
l_bat := lf_batched(crec.branch);
insert into mybranch (branch, capital, batched)
values (crec.branch, l_cap, l_bat );
end loop;
end;
Convert Oracle Data to DBF File [message #18363 is a reply to message #18358] Tue, 29 January 2002 09:16 Go to previous messageGo to next message
Fabio
Messages: 23
Registered: October 2001
Junior Member
Hello,
I need convert oracle data to dbf file. It's Possible??

Thanks

Fabio
Re: group by group [message #18364 is a reply to message #18352] Tue, 29 January 2002 09:45 Go to previous messageGo to next message
Rm69
Messages: 39
Registered: January 2002
Member
Table structure of zw30800p

CREATE TABLE ZW30800P (
STATUS VARCHAR2 (3),
STATUSD VARCHAR2 (30),
TYPE VARCHAR2 (1),
TYPED VARCHAR2 (30),
DEALREF VARCHAR2 (15),
UNIQUEREF NUMBER (9),
COMPANY VARCHAR2 (3),
BRANCH NUMBER (5),
BATCHNO NUMBER (9),
WAYBILLCD VARCHAR2 (20),
CLIENT NUMBER (7),
LOANSET1 VARCHAR2 (15),
SETDAT1 DATE,
SETAMT1 NUMBER (15,2),
LOANSET2 VARCHAR2 (15),
SETDAT2 DATE,
SETAMT2 NUMBER (15,2),
LOANSET3 VARCHAR2 (15),
SETDAT3 DATE,
SETAMT3 NUMBER (15,2),
IDNUMBER VARCHAR2 (15),
IDTYPE VARCHAR2 (2),
PRODUCT VARCHAR2 (5),
ACCOFFCD VARCHAR2 (3),
ACCOFFCDD VARCHAR2 (30),
BROKERCD VARCHAR2 (10),
SBROKERCD VARCHAR2 (10),
LOANOFF VARCHAR2 (10),
ZW_GROUP VARCHAR2 (15),
SUBGROUP VARCHAR2 (15),
CLOCKNO VARCHAR2 (15),
CAPITAL NUMBER (15,2),
PDINCL NUMBER (15,2),
PDEXCL NUMBER (15,2),
DEPOSIT NUMBER (15,2),
ZW_RECOVER NUMBER (15,2),
INTDUE NUMBER (15,2),
ADMINFEE NUMBER (15,2),
INSTAL NUMBER (15,2),
PAYFREQ VARCHAR2 (5),
FIRSTDUEDT DATE,
FLATRATE NUMBER (9,5),
EFTRATE NUMBER (9,5),
TERM NUMBER (3),
LOANUSE VARCHAR2 (1),
INSOLVENT VARCHAR2 (1),
SIGNDATE DATE,
STARTDATE DATE,
INTVALDATE DATE,
MATDATE DATE,
EMPPERIOD NUMBER (2),
WAGETYPE VARCHAR2 (1),
GROSSSAL NUMBER (15,2),
NETSAL NUMBER (15,2),
NONRECINC NUMBER (15,2),
TAXAMT NUMBER (15,2),
PENSAMT NUMBER (15,2),
PAYPOINT VARCHAR2 (12),
TITLE VARCHAR2 (4),
ZW_INITIAL VARCHAR2 (5),
SURNAME VARCHAR2 (50),
FIRSTNAME VARCHAR2 (50),
BIRTHDATE DATE,
CLTRESADR1 VARCHAR2 (30),
CLTRESADR2 VARCHAR2 (30),
CLTRESADR3 VARCHAR2 (30),
CLTRESCODE NUMBER (6),
CLTPOSADR1 VARCHAR2 (30),
CLTPOSADR2 VARCHAR2 (30),
CLTPOSADR3 VARCHAR2 (30),
CLTPOSCDE NUMBER (6),
HOMETEL VARCHAR2 (20),
SEX VARCHAR2 (1),
MARSTATUS VARCHAR2 (1),
HOMEOWNER VARCHAR2 (1),
WORKTEL VARCHAR2 (20),
OCCUPCODE VARCHAR2 (3),
OCCUPCODED VARCHAR2 (3),
WRKCTCNAME VARCHAR2 (50),
WORKADR1 VARCHAR2 (30),
WORKADR2 VARCHAR2 (30),
WORKADR3 VARCHAR2 (30),
WORKPOS NUMBER (6),
REPAYMTH VARCHAR2 (3),
BNKMETHOD VARCHAR2 (3),
BANKBRANCH NUMBER (7),
BANKACTYPE VARCHAR2 (4),
BANKACTYPD VARCHAR2 (30),
BANKACC VARCHAR2 (20),
ACCHOLDER VARCHAR2 (30),
CHKLPR VARCHAR2 (5),
CHKOAF VARCHAR2 (3),
CHKSBROKER VARCHAR2 (10),
CHKSGROUP VARCHAR2 (15),
CHKCLOCK VARCHAR2 (15),
CHKBRANCH NUMBER (5),
CHKIDNO VARCHAR2 (15),
CHKNETSAL NUMBER (15,2),
CHKBNKBRN NUMBER (7),
CHKBNKACC VARCHAR2 (20),
CHKTERM NUMBER (3),
CHKINSTAL NUMBER (15,2),
CHKLOANAMT NUMBER (15,2),
CHKSURNAME VARCHAR2 (50),
CHK1STNAM VARCHAR2 (50),
FREQCHG1 VARCHAR2 (3),
FREQAMT1 NUMBER (15,2),
FREQCHG2 VARCHAR2 (3),
FREQAMT2 NUMBER (15,2),
FREQCHG3 VARCHAR2 (3),
FREQAMT3 NUMBER (15,2),
FREQCHG4 VARCHAR2 (3),
FREQAMT4 NUMBER (15,2),
FREQCHG5 VARCHAR2 (3),
FREQAMT5 NUMBER (15,2),
CHARGE1 VARCHAR2 (3),
CHGAMT1 NUMBER (15,2),
CHARGE2 VARCHAR2 (3),
CHGAMT2 NUMBER (15,2),
CHARGE3 VARCHAR2 (3),
CHGAMT3 NUMBER (15,2),
CHARGE4 VARCHAR2 (3),
CHGAMT4 NUMBER (15,2),
CHARGE5 VARCHAR2 (3),
CHGAMT5 NUMBER (15,2),
PQUSER VARCHAR2 (10),
PQDATE DATE,
PQTIME NUMBER (6),
BAUSER VARCHAR2 (10),
BADATE DATE,
BATIME NUMBER (6),
SCUSER VARCHAR2 (10),
SCDATE DATE,
SCTIME NUMBER (6),
CAPUSER VARCHAR2 (10),
CAPDATE DATE,
CAPTIME NUMBER (6),
REJWAYB VARCHAR2 (20),
REJBTCH NUMBER (9),
REJUSER VARCHAR2 (10),
REJDATE DATE,
REJTIM NUMBER (6),
CHKUSER VARCHAR2 (10),
CHKDATE DATE,
CHKTIME NUMBER (6),
RETUSER VARCHAR2 (10),
RETDATE DATE,
RETTIME NUMBER (6),
DISUSER VARCHAR2 (10),
DISDATE DATE,
DISTIME NUMBER (6),
KNOWNAME VARCHAR2 (50),
EFTTYPE VARCHAR2 (2),
ISDAY NUMBER (2),
CEDATE DATE,
PEYEARS NUMBER (2),
PREMPL VARCHAR2 (30),
EMPMNTH NUMBER (2),
CELLNO VARCHAR2 (20),
EMAIL VARCHAR2 (30),
HOMOWNER VARCHAR2 (1),
HOMTYP VARCHAR2 (1),
HOMTYPD VARCHAR2 (30),
YPADDR NUMBER (2),
YCADDR NUMBER (2),
PROCNT VARCHAR2 (5),
NODEPEN NUMBER (2),
CCHOLD VARCHAR2 (1),
CCNUMB VARCHAR2 (20),
CCTYPE VARCHAR2 (1),
CCEXPR DATE,
SBBRAN NUMBER (7),
SBBRAND VARCHAR2 (30),
EPLANL VARCHAR2 (1),
PAYSLIP VARCHAR2 (1),
PENSNR VARCHAR2 (1),
EPHIST VARCHAR2 (1),
EPLANP VARCHAR2 (1),
EPLAN VARCHAR2 (1),
EPINCM NUMBER (15,2),
URUSR VARCHAR2 (10),
URWSID VARCHAR2 (10),
URDATE DATE,
URTIME NUMBER (6),
PRUSER VARCHAR2 (10),
PRWSID VARCHAR2 (10),
PRDATE DATE,
PRTIME NUMBER (6),
CRSCORE NUMBER (4),
CRSCOREAPP VARCHAR2 (1),
RANDNO NUMBER (3),
RCUSER VARCHAR2 (10),
RCWSID VARCHAR2 (10),
RCDATE DATE,
LRDATE DATE,
RCTIME NUMBER (6),
AN1308 VARCHAR2 (20),
AN2308 VARCHAR2 (20),
AN3308 VARCHAR2 (20),
AN4308 VARCHAR2 (20),
AN5308 VARCHAR2 (20),
AN6308 VARCHAR2 (20),
AN7308 VARCHAR2 (20),
AN8308 VARCHAR2 (20),
AN9308 VARCHAR2 (20),
AN0308 VARCHAR2 (20),
AH1308 VARCHAR2 (30),
AH2308 VARCHAR2 (30),
AH3308 VARCHAR2 (30),
AH4308 VARCHAR2 (30),
AH5308 VARCHAR2 (30),
AH6308 VARCHAR2 (30),
AH7308 VARCHAR2 (30),
AH8308 VARCHAR2 (30),
AH9308 VARCHAR2 (30),
AH0308 VARCHAR2 (30),
DA1308 NUMBER (15,2),
DA2308 NUMBER (15,2),
DA3308 NUMBER (15,2),
DA4308 NUMBER (15,2),
DA5308 NUMBER (15,2),
DA6308 NUMBER (15,2),
DA7308 NUMBER (15,2),
DA8308 NUMBER (15,2),
DA9308 NUMBER (15,2),
DA0308 NUMBER (15,2),
LASTCHGUSR VARCHAR2 (10),
LASTCHGDAT DATE,
LASTCHGTIM NUMBER (6)
Re: group by group [message #18372 is a reply to message #18358] Tue, 29 January 2002 13:20 Go to previous messageGo to next message
Rm69
Messages: 39
Registered: January 2002
Member
Tried this method the procedure runs for a very long time.Dont know whether its doing anything or not. Is there somthing that l'm doing wrong? This proc is supposed to sum and group by branch. So l'll have branch 001.002,003,etc plus the sum,summary_date etc

CREATE OR REPLACE procedure Mis_WorkFlowCapital (v_start_date IN DATE, v_end_date IN DATE) is
l_cap number;
l_bat number;
l_pre number;
l_sca number;
l_chk number;
l_rul number;
l_err number;
l_dis number;
l_act number;
l_ref number;
l_rej number;

cursor c1
is

select distinct(branch) branch from zw30800p
;

function lf_received (i_branch in varchar2) return number is
l_sum number;
begin
SELECT sum(CAPITAL) capital
into l_sum
from ZW30800P
where SCDATE between v_start_date
and v_start_date
and branch = i_branch;
return(l_sum);
end lf_received
;

function lf_prequota (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'PRE'
and PQDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_prequota
;

function lf_batched (i_branch in varchar2) return number is
l_sum number;
begin
SELECT sum(CAPITAL) capital
into l_sum
from ZW30800P
where status = 'BAT'
and BADATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_batched
;

function lf_scanned_received (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'SCA'
and SCDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_scanned_received
;

function lf_captured (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'CAP'
and CAPDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_captured
;

function lf_checked (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'CHK'
and CHKDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_checked
;

function lf_rules (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'RUL'
and LASTCHGDAT between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_rules
;

function lf_returned (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'ERR'
and RETDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_returned
;

function lf_disburse (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'DIS'
and LASTCHGDAT between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_disburse
;

function lf_active (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'ACT'
and DISDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_active
;

function lf_pre_reject (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'REF'
and PRDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_pre_reject
;

function lf_reject (i_branch in varchar2) return number is
l_sum number;
begin
select Sum(CAPITAL) capital
into l_sum
from ZW30800P
where STATUS = 'REJ'
and REJDATE between v_start_date
and v_end_date
and branch = i_branch;
return(l_sum);
end lf_reject
;

begin
for crec in c1
loop
l_cap := lf_received(crec.branch);
l_bat := lf_batched (crec.branch);
l_pre := lf_received(crec.branch);
l_sca := lf_received(crec.branch);
l_chk := lf_received(crec.branch);
l_rul := lf_received(crec.branch);
l_err := lf_received(crec.branch);
l_dis := lf_received(crec.branch);
l_act := lf_received(crec.branch);
l_ref := lf_received(crec.branch);
l_rej := lf_received(crec.branch);

insert into Mis_WrkFlow_Cap (branch,received,prequota,batched,scanned_received,captured,
checked,rules,disburse,active,pre_reject,rejected,change_date)
values (crec.branch,l_cap,l_bat,l_pre,l_sca,l_chk,l_rul,l_err,l_dis,l_act,l_ref,l_rej,sysdate);
end loop;
end;

This Is the Original proc

Procedure Mis_WorkFlowCapital_Ray
(v_start_date DATE,v_end_date DATE)

AS

BEGIN

insert into MIS_WRKFLW_WITHCOUNT(branch,Received,rec_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where SCDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,Prequota,prequota_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'PRE'
and PQDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT (branch,Batched,batched_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'BAT'
and BADATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,scanned_received,scanrec_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'SCA'
and SCDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,captured,cpa_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'CAP'
and CAPDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,checked,chkd_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'CHK'
and CHKDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,rules,rules_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'RUL'
and LASTCHGDAT between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,disburse,dis_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'ERR'
and RETDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,active,active_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'DIS'
and LASTCHGDAT between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,pre_reject,prerej_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'ACT'
and DISDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,rejected,rej_count,summary_date)
select branch,sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'REF'
and PRDATE between v_start_date and v_end_date
group by branch;

insert into MIS_WRKFLW_WITHCOUNT(branch,rec_count,summary_date)
select sum(CAPITAL),count(capital),sysdate
from ZW30800P
where STATUS = 'REJ'
and REJDATE between v_start_date and v_end_date
group by branch;

end;
Re: group by group [message #18393 is a reply to message #18358] Wed, 30 January 2002 04:20 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
To see if its working or not, change the query (c1 cursor) and include a where clause WHERE BRANCH = '001' (or whatever). Run the procedure. Look to see if it worked. Rollback. Change the query back (assuming it worked) and let the procedure chug.
Previous Topic: how do i compare the "char" type column of 2 tables
Next Topic: Re: how one can call stored procedure in VB
Goto Forum:
  


Current Time: Thu Mar 28 05:06:42 CDT 2024