Bug #10682 TRUNCATE in the SP
Submitted: 17 May 2005 9:27 Modified: 17 May 2005 19:57
Reporter: hwang hwang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.4-beta OS:Linux (Redhat linux9)
Assigned to: CPU Architecture:Any

[17 May 2005 9:27] hwang hwang 
Description:
Use Mysql5.0.4-beta
mysql>Start Transaction;
mysql>call call p_bt_openaccount("200505");
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

Use Mysql5.0.1-alpha
mysql>Start Transaction;
mysql>call call p_bt_openaccount("200505");
Query OK, 0 rows affected (0.19 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

How to repeat:
>CREATE TABLE `ts_a_realbill05` (
  `accountid` varchar(64) NOT NULL default '',
  `cotno` varchar(16) NOT NULL default '0',
  `from_phone` varchar(20) NOT NULL default '0',
  `to_phone` varchar(30) NOT NULL default '0',
  `area_code` varchar(10) default NULL,
  `feetype` char(2) NOT NULL default '0',
  `calltype` char(1) NOT NULL default '0',
  `fee` decimal(10,2) NOT NULL default '0.00',
  `dis_fee` decimal(10,2) NOT NULL default '0.00',
  `start_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `time_length` int(10) NOT NULL default '0',
  `tag` char(1) NOT NULL default '0',
  `all_timelength` int(10) NOT NULL default '0',
  PRIMARY KEY  (`accountid`),
  KEY `idx_ts_a_reallbill01_from_phone` (`from_phone`),
  KEY `idx_ts_a_reallbill01_start_time` (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

>CREATE TABLE `tp_a_combill` (
  `cotno` varchar(16) NOT NULL default '',
  `phonecode` varchar(20) NOT NULL default '',
  `billmonth` varchar(6) NOT NULL default '',
  `fee01` decimal(10,2) NOT NULL default '0.00',
  `fee02` decimal(10,2) NOT NULL default '0.00',
  `fee03` decimal(10,2) NOT NULL default '0.00',
  `fee04` decimal(10,2) NOT NULL default '0.00',
  `fee05` decimal(10,2) NOT NULL default '0.00',
  `fee06` decimal(10,2) NOT NULL default '0.00',
  `fee07` decimal(10,2) NOT NULL default '0.00',
  `fee08` decimal(10,2) NOT NULL default '0.00',
  `fee09` decimal(10,2) NOT NULL default '0.00',
  `fee10` decimal(10,2) NOT NULL default '0.00',
  `dis_fee01` decimal(10,2) NOT NULL default '0.00',
  `dis_fee02` decimal(10,2) NOT NULL default '0.00',
  `dis_fee03` decimal(10,2) NOT NULL default '0.00',
  `dis_fee04` decimal(10,2) NOT NULL default '0.00',
  `dis_fee05` decimal(10,2) NOT NULL default '0.00',
  `dis_fee06` decimal(10,2) NOT NULL default '0.00',
  `dis_fee07` decimal(10,2) NOT NULL default '0.00',
  `dis_fee08` decimal(10,2) NOT NULL default '0.00',
  `dis_fee09` decimal(10,2) NOT NULL default '0.00',
  `dis_fee10` decimal(10,2) NOT NULL default '0.00',
  KEY `phonecode` (`phonecode`,`billmonth`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

>CREATE TABLE `ts_a_combill` (
  `cotno` varchar(16) NOT NULL default '',
  `phonecode` varchar(20) NOT NULL default '',
  `billmonth` varchar(6) NOT NULL default '',
  `fee01` decimal(10,2) NOT NULL default '0.00',
  `fee02` decimal(10,2) NOT NULL default '0.00',
  `fee03` decimal(10,2) NOT NULL default '0.00',
  `fee04` decimal(10,2) NOT NULL default '0.00',
  `fee05` decimal(10,2) NOT NULL default '0.00',
  `fee06` decimal(10,2) NOT NULL default '0.00',
  `fee07` decimal(10,2) NOT NULL default '0.00',
  `fee08` decimal(10,2) NOT NULL default '0.00',
  `fee09` decimal(10,2) NOT NULL default '0.00',
  `fee10` decimal(10,2) NOT NULL default '0.00',
  `dis_fee01` decimal(10,2) NOT NULL default '0.00',
  `dis_fee02` decimal(10,2) NOT NULL default '0.00',
  `dis_fee03` decimal(10,2) NOT NULL default '0.00',
  `dis_fee04` decimal(10,2) NOT NULL default '0.00',
  `dis_fee05` decimal(10,2) NOT NULL default '0.00',
  `dis_fee06` decimal(10,2) NOT NULL default '0.00',
  `dis_fee07` decimal(10,2) NOT NULL default '0.00',
  `dis_fee08` decimal(10,2) NOT NULL default '0.00',
  `dis_fee09` decimal(10,2) NOT NULL default '0.00',
  `dis_fee10` decimal(10,2) NOT NULL default '0.00',
  KEY `phonecode` (`phonecode`,`billmonth`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

>CREATE TABLE `td_a_billingcycle` (
  `start_date` char(8) NOT NULL default '0',
  `end_date` char(8) NOT NULL default '0',
  `cycle` char(6) NOT NULL default '',
  `flag` char(1) NOT NULL default '0',
  PRIMARY KEY  (`cycle`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

>INSERT INTO `td_a_billingcycle` VALUES ('20050501', '20050531', '200505', '0');

>delimiter //
>Create Procedure p_bt_openaccount(In  v_billmonth	char)
Begin
	truncate table tp_a_combill;

        delete from ts_a_combill where billmonth=v_billmonth;

        If Right(v_billmonth,2)="01" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill01 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill01 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill01 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill01 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill01 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill01 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill01 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill01 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill01 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill01 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="02" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill02 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill02 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill02 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill02 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill02 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill02 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill02 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill02 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill02 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill02 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="03" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill03 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill03 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill03 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill03 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill03 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill03 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill03 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill03 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill03 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill03 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="04" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill04 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill04 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill04 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill04 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill04 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill04 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill04 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill04 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill04 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill04 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="05" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill05 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill05 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill05 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill05 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill05 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill05 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill05 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill05 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill05 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill05 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="06" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill06 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill06 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill06 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill06 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill06 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill06 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill06 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill06 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill06 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill06 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="07" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill07 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill07 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill07 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill07 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill07 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill07 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill07 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill07 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill07 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill07 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="08" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill08 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill08 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill08 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill08 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill08 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill08 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill08 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill08 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill08 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill08 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="09" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill09 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill09 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill09 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill09 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill09 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill09 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill09 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill09 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill09 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill09 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="10" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill10 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill10 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill10 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill10 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill10 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill10 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill10 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill10 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill10 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill10 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="11" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill11 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill11 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill11 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill11 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill11 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill11 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill11 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill11 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill11 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill11 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        If Right(v_billmonth,2)="12" Then
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        fee,0,0,0,0,0,0,0,0,0,
                        dis_fee,0,0,0,0,0,0,0,0,0
                From    ts_a_realbill12 Where feetype = "01";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,fee,0,0,0,0,0,0,0,0,
                        0,dis_fee,0,0,0,0,0,0,0,0
                From    ts_a_realbill12 Where feetype = "02";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,fee,0,0,0,0,0,0,0,
                        0,0,dis_fee,0,0,0,0,0,0,0
                From    ts_a_realbill12 Where feetype = "03";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,fee,0,0,0,0,0,0,
                        0,0,0,dis_fee,0,0,0,0,0,0
                From    ts_a_realbill12 Where feetype = "04";

                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,fee,0,0,0,0,0,
                        0,0,0,0,dis_fee,0,0,0,0,0
                From    ts_a_realbill12 Where feetype = "05";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,fee,0,
                        0,0,0,0,0,0,0,0,dis_fee,0
                From    ts_a_realbill12 Where feetype = "06";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,fee,0,0,0,0,
                        0,0,0,0,0,dis_fee,0,0,0,0
                From    ts_a_realbill12 Where feetype = "07";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,fee,0,0,
                        0,0,0,0,0,0,0,dis_fee,0,0
                From    ts_a_realbill12 Where feetype = "09";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill12 Where feetype = "10";
                
                Insert Into tp_a_combill
                Select  cotno,from_phone,v_billmonth,
                        0,0,0,0,0,0,0,0,0,fee,
                        0,0,0,0,0,0,0,0,0,dis_fee
                From    ts_a_realbill12 Where feetype Not In ("01","02","03","04","05","06","07","08","09","10");
        End If;

        Insert Into ts_a_combill
        Select  cotno,phonecode,billmonth,
                Sum(fee01),Sum(fee02),Sum(fee03),Sum(fee04),Sum(fee05),
                Sum(fee06),Sum(fee07),Sum(fee08),Sum(fee09),Sum(fee10),
                Sum(dis_fee01),Sum(dis_fee02),Sum(dis_fee03),Sum(dis_fee04),Sum(dis_fee05),
                Sum(dis_fee06),Sum(dis_fee07),Sum(dis_fee08),Sum(dis_fee09),Sum(dis_fee10)
        From    tp_a_combill
        Group by  cotno,phonecode,billmonth;
        
        Insert Into ts_a_combill
        Select a.cotno,a.phonecode,v_billmonth,
               0,0,0,0,0,0,0,0,0,0,
               0,0,0,0,0,0,0,0,0,0
	From   tf_f_userphone a Left Join ts_a_combill b On a.phonecode=b.phonecode
	Where  b.phonecode is null;

        Update td_a_billingcycle 
        Set flag='1' 
        Where cycle=v_billmonth;
End//

mysql>Start Transaction//
mysql>call call p_bt_openaccount("200505")//
[17 May 2005 14:57] MySQL Verification Team 
Could you please provide the script How to Repeat as attached files
I am having problems for to paste from the browser to my terminal
getting syntax error.

Thanks in advance.
[17 May 2005 19:57] Sergei Golubchik 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

according to the http://dev.mysql.com/doc/mysql/en/truncate.html

 Truncate operations are not transaction-safe; you get an error if you have an active transaction or an active table lock.
[18 May 2005 2:11] hwang hwang 
Thank you!