Re: mysql 5.5 to mysql 5.7

[mark cabantog]

------------ this are the tables -----------


CREATE TABLE `acct_invoice` (
`inv_id` int(11) NOT NULL AUTO_INCREMENT,
`inv_no` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`inv_to` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`inv_adres` text CHARACTER SET utf8,
`inv_contact` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`inv_date` date DEFAULT NULL,
`or_num` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`or_date` date DEFAULT NULL,
`typ` int(1) DEFAULT '0',
`xuser` int(11) DEFAULT NULL,
`stat` int(1) DEFAULT '0' COMMENT '0-Pending, 1-Paid, -1 - Cancel',
`acc_yr` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`details` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`cncl` int(1) DEFAULT '0',
`amnt` int(11) DEFAULT NULL,
`cancel_by` int(11) DEFAULT NULL,
`cancel_date` date DEFAULT NULL,
PRIMARY KEY (`inv_id`),
KEY `inv_no` (`inv_no`)
) ENGINE=InnoDB AUTO_INCREMENT=5299 DEFAULT CHARSET=latin1;



CREATE TABLE `acct_invoice_det` (
`inv_det_id` int(11) NOT NULL AUTO_INCREMENT,
`inv_no` int(11) DEFAULT NULL,
`acct_code` float(10,4) DEFAULT NULL,
`inv_descript` text CHARACTER SET utf8,
`amnt` float(10,2) DEFAULT NULL,
`typ` int(1) DEFAULT '0',
`xuser` int(11) DEFAULT NULL,
`stu_id` int(11) DEFAULT NULL,
`book_isbn` text CHARACTER SET utf8,
`xdate` date DEFAULT NULL,
`acc_yr` tinytext CHARACTER SET utf8,
`transport` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`details` varchar(250) CHARACTER SET utf8 DEFAULT NULL,
`cncl` int(5) DEFAULT '0',
`tution_discount` int(11) DEFAULT '0',
PRIMARY KEY (`inv_det_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24384 DEFAULT CHARSET=latin1;


CREATE TABLE `acct_rv` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`or_num` int(11) DEFAULT NULL,
`or_date` date DEFAULT NULL,
`acc_yr` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`cash_amnt` float(10,2) DEFAULT '0.00',
`credit_amnt` float(10,2) DEFAULT '0.00',
`credit_typ` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`cheque_amnt` float(10,2) DEFAULT '0.00',
`cheque_typ` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`stat` int(1) DEFAULT '1' COMMENT '1 = ok, 0=cancel',
`xuser` int(11) DEFAULT NULL,
`inv_no` int(11) DEFAULT NULL,
`inv_date` date DEFAULT NULL,
`details` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`cncl` int(11) DEFAULT '0',
`credit_serial` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`rvname` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`rvaddress` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
`update_by` int(11) DEFAULT NULL,
`cancel_by` int(11) DEFAULT NULL,
`cancel_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `or_num` (`or_num`),
KEY `inv_no` (`inv_no`)
) ENGINE=InnoDB AUTO_INCREMENT=4293 DEFAULT CHARSET=latin1;














--------------- this is the query --------------
SELECT acct_invoice.inv_no AS 'Inv No',acct_invoice.inv_to AS 'Parent name', acct_invoice.inv_contact AS 'Contact', acct_invoice.inv_date AS 'Date', SUM(acct_invoice_det.amnt) AS 'Amount', acct_invoice.parent_id , acct_invoice.inv_adres, acct_invoice.details,acct_invoice.acc_yr,
(SELECT SUM(cash_amnt)+SUM(credit_amnt)+ SUM(cheque_amnt) AS total FROM acct_rv AS rv WHERE rv.inv_no=acct_invoice_det.inv_no) AS paid,(SUM(acct_invoice_det.amnt)-(SELECT SUM(cash_amnt)+SUM(credit_amnt)+ SUM(cheque_amnt) AS total FROM acct_rv AS rv WHERE rv.inv_no=acct_invoice_det.inv_no)) AS balance ,acct_invoice.acc_yr
FROM acct_invoice_det INNER JOIN acct_invoice ON (acct_invoice_det.inv_no = acct_invoice.inv_no)


WHERE acct_invoice.cncl<>1
GROUP BY inv_to,inv_contact,inv_date,acct_invoice.inv_no
ORDER BY acct_invoice.inv_no DESC











both server have same table structure and i run the same query...