i kept getting this error
Table dropped.
Table created.
Input truncated to 1 characters
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 36
I need to submit this report a.s.a.p..
can anyone help me?
my program
----------------------------------------------------------------
drop table wh_Billing;
create table wh_Billing
(
MASTER_ID VARCHAR2(16),
WH_SUBS_CATEGORY VARCHAR2( 8 ),
BAD_PHONE_FLAG VARCHAR2(4),
TOT_DUE_AMT NUMBER(16,4),
AGING0 NUMBER(16,4),
AGING1 NUMBER(16,4),
AGING2 NUMBER(16,4),
AGING3 NUMBER(16,4),
AGING4 NUMBER(16,4),
AGING5 NUMBER(16,4),
AGING6 NUMBER(16,4),
AGING7 NUMBER(16,4),
AGING8 NUMBER(16,4),
AGING9 NUMBER(16,4),
AGING10 NUMBER(16,4)
);
declare
cursor cur is
select distinct raccount.master_id, wh_subs_category, curr_id, ss_no
from raccount,jmastersubsid
where curr_id ='RM'
AND raccount.master_id = jmastersubsid.master_id
AND raccount.ss_no = jmastersubsid.subs_id
AND bad_phone_flag = 'B';
t_masterid jmastersubsid.master_id%type;
t_subscategory jmastersubsid.wh_subs_category%type;
t_bad_phone_flag raccount.bad_phone_flag%type:='B';
t_tot_due_amt number(16):=0;
t_aging0 number(16):=0;
t_aging1 number(16):=0;
t_aging2 number(16):=0;
t_aging3 number(16):=0;
t_aging4 number(16):=0;
t_aging5 number(16):=0;
t_aging6 number(16):=0;
t_aging7 number(16):=0;
t_aging8 number(16):=0;
t_aging9 number(16):=0;
t_aging10 number(16):=0;
ins_ctr number:=0;
begin
for rec in cur
loop
begin
select distinct NVL(raccount.master_id,'UNKNOWN') as Master_id,
nvl(wh_subs_category,'UNKNOWN') as wh_subs_category ,
--nvl(bad_phone_flag,'UNKNOWN') as billing_platform ,
nvl(TO_CHAR(sum(tot_due_amt),'999,999,999.99'),0) as tot_due_amt ,
nvl(TO_CHAR(sum(aging0),'999,999,999.99'),0) as aging0 ,
nvl(TO_CHAR(sum(aging1),'999,999,999.99'),0) as aging1,
nvl(TO_CHAR(sum(aging2),'999,999,999.99'),0) as aging2,
nvl(TO_CHAR(sum(aging3),'999,999,999.99'),0) as aging3,
nvl(TO_CHAR(sum(aging4),'999,999,999.99'),0) as aging4,
nvl(TO_CHAR(sum(AGING5),'999,999,999.99'),0) as aging5,
nvl(TO_CHAR(sum(aging6),'999,999,999.99'),0) as aging6,
nvl(TO_CHAR(sum(aging7),'999,999,999.99'),0) as aging7,
nvl(TO_CHAR(sum(aging8),'999,999,999.99'),0) as aging8,
nvl(TO_CHAR(sum(aging9),'999,999,999.99'),0) as aging9,
nvl(TO_CHAR(sum(aging10),'999,999,999.99'),0) as aging10
into t_masterid,t_subscategory,t_tot_due_amt,t_aging0,t_aging1,t_aging2,t_aging3,t_aging4,
t_aging5,t_aging6,t_aging7,t_aging8,t_aging9,t_aging10
from raccount,jmastersubsid
where curr_id ='RM'
AND raccount.master_id = jmastersubsid.master_id
AND raccount.ss_no = jmastersubsid.subs_id
AND bad_phone_flag = t_bad_phone_flag
GROUP BY raccount.master_id,wh_subs_category;
exception when no_data_found then
t_subscategory := 'OTHERS';
end;
insert into wh_Billing_p
(
MASTER_ID,
WH_SUBS_CATEGORY,
BAD_PHONE_FLAG,
TOT_DUE_AMT,
AGING0,
AGING1,
AGING2,
AGING3,
AGING4,
AGING5,
AGING6,
AGING7,
AGING8,
AGING9,
AGING10
)
values
(
t_masterid,
t_subscategory,
t_bad_phone_flag,
t_tot_due_amt,
t_aging0,
t_aging1,
t_aging2,
t_aging3,
t_aging4,
t_aging5,
t_aging6,
t_aging7,
t_aging8,
t_aging9,
t_aging10
);
ins_ctr := ins_ctr + 1;
if ins_ctr = 100 then
commit;
ins_ctr :=0;
end if;
end loop;
commit;
end;
/
[Updated on: Wed, 11 October 2006 20:53] Report message to a moderator |
] 
Blog
Search
Help
Members
Register
Login
Home
