Any idea?

[shamuntoha]

Any idea why getting a error while select, insert, using few stored procedure. Exception "trying to fetch row:null"

 

select destno,duration,compid,agentid,
@sab:=(select subsubcomp from users where users.ugroup='reseller' and users.agentid=cdr.agentid and users.compid=cdr.compid limit 1) sb,
@scd:=(select rateid from subrcomp where subrcomp.agentid=cdr.agentid and subrcomp.balance2=@sab) sa,
(case when @sab is NULL then @sr1:=substring_index(substring_index(@sw1:=concat(0,',',0,',',0), ',', 1), ',', -1) else 
(case when @scd is NULL then @sr1:=substring_index(substring_index(@sw1:=concat(0,',',0,',',0), ',', 1), ',', -1) else 
@sr1:=substring_index(substring_index(@sw1:=f_subrrate(cdr.agentid,@sab,@scd,cdr.destno), ',', 1), ',', -1) end) end) sRCollecting,
@sr2:=substring_index(substring_index(@sw1, ',', 2), ',', -1) RSetup,@sr3:=substring_index(substring_index(@sw1, ',', 3), ',', -1) 
sRRate,concat((@sr3+@sr1)*duration/60+@sr2) as sRcost
from cdr where cdr.date='2008-08-09' 
and cdr.duration>0 and convert(destno, SIGNED INTEGER) is not null;

 

insert ignore into SlaveCdr (vtemp4,vtemp3,vtemp2,vtemp1,CDRID,CDRTIME,COMPID,Duration,Switch,Time,Date,PIN,DestNo,LineOut,CarrierOut,CallRate,
CallCost, DNIS, BalLeft, CarrierIn, LineIn, Cause,CLI,slavecdr.AgentId, BatchId, ExpiryDate,Temp5,BuyCollecting ,BuySetup,
BuyRate,BuyCost,Temp3,OwnCollecting,OwnSetup,OwnRate,OwnCost,Temp4,vcollecting,vsetup,vrate,vcost,Outboundno,Blegzone,Blegrateid,slavecdr.ID,vtemp5) 
select @ab:=NULL,@ef:=NULL,@ij:=NULL,concat(@cd:=NULL,@b1:=NULL,@b3:=NULL,@r1:=NULL,@r3:=NULL,@p1:=NULL,@p3:=NULL,@v1:=NULL,@w1:=NULL,@y1:=NULL),CDRID,CDRTIME,COMPID,Duration,Switch,Time,Date,PIN,DestNo,LineOut,CarrierOut,CallRate ,
CallCost, DNIS, BalLeft, CarrierIn, LineIn, Cause,CLI,cdr.AgentId, BatchId, ExpiryDate,
@ij:=(select agentid from srcomp where srcomp.agentid=cdr.agentid limit 1) temp5,
(case when @ij is null then @b1:=substring_index(substring_index(@v1:=concat(0,',',0,',',0), ',', 1), ',', -1) else @b1:=substring_index(substring_index(@v1:=f_srrate(cdr.agentid,destno), ',', 1), ',', -1) end) BuyCollecting,
@b2:=substring_index(substring_index(@v1, ',', 2), ',', -1) BuySetup,
@b3:=substring_index(substring_index(@v1, ',', 3), ',', -1) BuyRate,
concat((@b3+@b1)*duration/60+@b2) as BuyCost,
(select (case when find_in_set(left(cdr.time,2),bcomp.hours) is null then @ef:=NULL else @ef:=bcomp.rateid end) from bcomp where bcomp.carrier=cdr.carrierout and bcomp.node=cdr.outboundno and not find_in_set(left(cdr.time,2),bcomp.hours)='0' limit 1) Tmp3,
(case when @ef is NULL then @o1:=substring_index(substring_index(@x1:=concat(0,',',0,',',0), ',', 1), ',', -1) else @o1:=substring_index(substring_index(@x1:=f_brate(@ef,cdr.destno), ',', 1), ',', -1) end) OCollecting,
@o2:=substring_index(substring_index(@x1, ',', 2), ',', -1) OSetup,@o3:=substring_index(substring_index(@x1, ',', 3), ',', -1) ORate,concat((@o3+@o1)*duration/60+@o2) as Ocost,
(select (case when find_in_set(left(cdr.time,2),vcomp.hours) is null then @gh:=NULL else @gh:=vcomp.rateid end) from vcomp where vcomp.agentid=cdr.agentid and vcomp.carrier=cdr.carrierout and vcomp.node=cdr.outboundno and not find_in_set(left(cdr.time,2),vcomp.hours)='0' limit 1) Tmp4,
(case when @gh is NULL then @p1:=substring_index(substring_index(@y1:=concat(0,',',0,',',0), ',', 1), ',', -1) else @p1:=substring_index(substring_index(@y1:=f_vrate(cdr.agentid,@gh,cdr.destno), ',', 1), ',', -1) end) vcollecting,
@p2:=substring_index(substring_index(@y1, ',', 2), ',', -1) vsetup,@p3:=substring_index(substring_index(@y1, ',', 3), ',', -1) pRate,if(@p1<>0 or @p2<>0 or @p3<>0 and cdr.duration>0,(case when ((@p3+@p1)*duration/60+@p2)<=1 then 1 else concat((@p3+@p1)*duration/60+@p2) end),0) vcost,
Outboundno,cdr.Blegzone,cdr.Blegrateid,cdr.ID,@gh:=NULL from cdr where cdr.date='2007-10-10' and cdr.duration>0 and convert(destno, SIGNED INTEGER) is not null;

[waynew]

 

 

http://bugs.mysql.com/bug.php?id=5974