Home » SQL & PL/SQL » SQL & PL/SQL » PLEASE HELP ME! ERROR STRIKES ME!!
icon5.gif  PLEASE HELP ME! ERROR STRIKES ME!! [message #197577] Wed, 11 October 2006 20:51 Go to next message
slam_buzz
Messages: 3
Registered: October 2006
Location: klang
Junior Member

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

Re: PLEASE HELP ME! ERROR STRIKES ME!! [message #197578 is a reply to message #197577] Wed, 11 October 2006 21:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How many rows does
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';

return?
Re: PLEASE HELP ME! ERROR STRIKES ME!! [message #197579 is a reply to message #197578] Wed, 11 October 2006 21:40 Go to previous messageGo to next message
slam_buzz
Messages: 3
Registered: October 2006
Location: klang
Junior Member

13 rows.
Re: PLEASE HELP ME! ERROR STRIKES ME!! [message #197587 is a reply to message #197579] Wed, 11 October 2006 22:22 Go to previous message
slam_buzz
Messages: 3
Registered: October 2006
Location: klang
Junior Member

nvm.. i've solved my probs. thanx ! Razz
Previous Topic: Tuning SQL query (merged 2 cross-posts)
Next Topic: Easier Way
Goto Forum:
  


Current Time: Fri Dec 09 13:27:08 CST 2016

Total time taken to generate the page: 0.13691 seconds