Problem translate query from Access to MySQL

Miguel61 · July 21, 2011, 9:56am

Hi there, I hope for your help.

I’m very sorry disturb, but I need translate SQL Query from Access DB to MySQL DB.

I need your helping because when execute SQL query in the Access DB the number of records imported is 204.786 and don’t have Orig_Int null value.

When execute SQL query in the MySQL DB the number of records imported is 275.333 and 1.588 records when the field Orig_Int is null, why?

All tables are identical in all databases, but in MyQL I have 70.547 rows extra.
I think problem with function aggregate in MySQL.

Can you help me?
Thanks in advance.

This is SQL Query in Access DB (output 204.786 rows and don’t have Orig_Int null value ):


INSERT INTO _Reg_L ( CFT_Cod, Orig_Int, DtInter, [A20 (Min)], A16_17 )

SELECT
Org.CFT_Cod,
IIf([R].[Art]='51M','51M',IIf([R].[Art]='450','MT','BT')) AS Orig_Int,
CDate(CStr(Day([R]![DtInter]))+'/'+CStr(Month([R]![DtInter]))+'/'+CStr(Year([R]![DtInter]))) AS DtInter,
Sum(IIf([R].[CDI]='P',[R].[C_BT]*[R].[DNF8 (sec)]/60,0)) AS A20,
Sum(IIf([R].[CDI] In ('P','B'),[R].[C_BT],0)) AS A16_17

FROM
_Reg AS R
INNER JOIN Org ON R.CFT_Cod = Org.CFT_Cod_old

WHERE
((R.Pre='N' Or R.Pre Is Null) AND
R.E_Cod Like 'T*' AND
R.C_I<>'F' AND
R.Pert='N' AND
R.Art In ('51M','450','41B','460','475','476'))

OR

((R.Pre='N' Or R.Pre Is Null) AND
R.E_Cod Like 'T*' AND
R.C_I<>'F' AND
R.Pert='N' AND
R.Art In ('51M','450','41B','460','475','476'))

GROUP BY
Org.CFT_Cod,
IIf([R].[Art]='51M','51M',IIf([R].[Art]='450','MT','BT')),
CDate(CStr(Day([R]![DtInter]))+'/'+CStr(Month([R]![DtInter]))+'/'+CStr(Year([R]![DtInter])))"

HAVING
(((Sum(IIf([R].[CDI]='P',[R].[C_BT]*[R].[DNF8 (sec)]/60,0)))>0))

OR

(((Sum(IIf([R].[CDI] In ('P','B'),[R].[C_BT],0)))>0))"

This is SQL Query translate from Access DB to MySQL DB and execute in MySQL DB (output 275.333 rows and 1.588 records when the field Orig_Int is null ):


INSERT INTO  
_Reg_L   
(  
  xID  
, DtUpdate 

, CFT_Cod  
, Orig_Int  

, DtInter  

, `A20(Min)`  
, A16_17   
)  
SELECT  
 0  
, R.DtUpdate 

, C.CFT_Cod 
 
, CASE  
  WHEN (R.Art='51M' AND R.CDI In('P','B')) THEN '51M'  
  WHEN (R.Art='450' AND R.CDI In('P','B')) THEN 'MT'  
  WHEN (R.Art NOT IN ('51M','450') AND R.CDI In('P','B')) THEN 'BT'  
  END Orig_Int  

, STR_TO_DATE(R.DtInter, '%Y-%m-%d %H:%i:%s') DtInter  

, SUM(IF(R.CDI='P', R.C_BT * `DNF8 (sec)`/60, 0)) A20  
, SUM(IF(R.CDI In('P','B'), R.C_BT, 0)) A16_17
  
FROM _Reg R  
INNER JOIN Org C ON R.CFT_Cod = Org.CFT_Cod_old  

WHERE 1 AND  
(  
(R.Pre='N' Or IsNull(R.Pre))  
AND R.E_Cod Like 'T%'  
AND R.C_I&lt;&gt;'F'  
AND R.Pert='N'  
AND R.Art In ('51M','450','41B','460','475','476')  
)   

  GROUP BY  
  C.CFT_Cod  
, Orig_Int  
, DtInter  

HAVING A20 &gt; 0 OR A16_17 &gt; 0  
ORDER BY DtInter DESC;

Miguel61 · July 21, 2011, 10:25am

thanks for help.

Guido: With your modify now I have affected rows 276.709 and don’t have records when the field Orig_Int is null.

r937: In the table name _Reg the field DtInter is DateTime format, I need write in the table name _Reg_L only value of the date (no time format).

Query:


INSERT INTO  
_Reg_L   
(  
  xID  
, DtUpdate 
, CFT_Cod  
, Orig_Int 
, DtInter  
, `A20(Min)`  
, A16_17   
)  
SELECT  
 0  
, R.DtUpdate 
, C.CFT_Cod  
, CASE  
  WHEN R.Art='51M'  THEN '51M'  
  WHEN R.Art='450'  THEN 'MT'  
  ELSE 'BT'  
  END Orig_Int  
, STR_TO_DATE(R.DtInter, '%Y-%m-%d %H:%i:%s') DtInter 
, SUM(IF(R.CDI='P', R.C_BT * `DNF8 (sec)`/60, 0)) A20  
, SUM(IF(R.CDI In('P','B'), R.C_BT, 0)) A16_17
  
FROM _Reg R  
INNER JOIN Org C ON R.CFT_Cod = Org.CFT_Cod_old  

WHERE 1 AND  
(  
(R.Pre='N' Or IsNull(R.Pre))  
AND R.E_Cod Like 'T%'  
AND R.C_I&lt;&gt;'F'  
AND R.Pert='N'  
AND R.Art In ('51M','450','41B','460','475','476')  
)   

  GROUP BY  
  C.CFT_Cod  
, Orig_Int  
, DtInter  

HAVING A20 &gt; 0 OR A16_17 &gt; 0  
ORDER BY DtInter DESC;

DarthGuido · July 21, 2011, 10:04am

Don’t know about the extra lines, but this


IIf([R].[Art]='51M','51M',IIf([R].[Art]='450','MT','BT')) AS Orig_Int,

shouldn’t become


CASE  
  WHEN (R.Art='51M' AND R.CDI In('P','B')) THEN '51M'  
  WHEN (R.Art='450' AND R.CDI In('P','B')) THEN 'MT'  
  WHEN (R.Art NOT IN ('51M','450') AND R.CDI In('P','B')) THEN 'BT'  
END Orig_Int

but


CASE  
  WHEN R.Art='51M'  THEN '51M'  
  WHEN R.Art='450'  THEN 'MT'  
  ELSE 'BT'  
END Orig_Int

Unless you changed something in the logic of the query while migrating

r937 · July 21, 2011, 10:06am

in access query, one of the GROUP BY columns is a date

in mysql query, the same GROUP BY column is a datetime

r937 · July 21, 2011, 10:46am

if that’s what you need to do, then i would suggest that you change this –

STR_TO_DATE(R.DtInter, '%Y-%m-%d [COLOR="Red"]%H:%i:%s[/COLOR]') DtInter

Miguel61 · July 21, 2011, 12:00pm

Ok, I try this with your suggestion, but I have error:

Query:


INSERT INTO  
_Reg_L   
(  
  xID  
, DtUpdate 
, CFT_Cod  
, Orig_Int 
, DtInter  
, `A20(Min)`  
, A16_17   
)  
SELECT  
 0  
, R.DtUpdate 
, C.CFT_Cod  
, CASE  
  WHEN R.Art='51M'  THEN '51M'  
  WHEN R.Art='450'  THEN 'MT'  
  ELSE 'BT'  
  END Orig_Int  
, STR_TO_DATE(R.DtInter, '%Y-%m-%d') DtInter 
, SUM(IF(R.CDI='P', R.C_BT * `DNF8 (sec)`/60, 0)) A20  
, SUM(IF(R.CDI In('P','B'), R.C_BT, 0)) A16_17
  
FROM _Reg R  
INNER JOIN Org C ON R.CFT_Cod = Org.CFT_Cod_old  

WHERE 1 AND  
(  
(R.Pre='N' Or IsNull(R.Pre))  
AND R.E_Cod Like 'T%'  
AND R.C_I&lt;&gt;'F'  
AND R.Pert='N'  
AND R.Art In ('51M','450','41B','460','475','476')  
)   

  GROUP BY  
  C.CFT_Cod  
, Orig_Int  
, DtInter  

HAVING A20 &gt; 0 OR A16_17 &gt; 0  
ORDER BY DtInter DESC;

[Err] 1292 - Truncated incorrect date value: ‘2011-01-25 09:07:00’

r937 · July 21, 2011, 2:01pm

i’m guessing (because i can’t see your data rows) but i think this might have to do with using a column alias that’s the same as the column

i.e. you have

INSERT INTO ...
SELECT ...
, STR_TO_DATE(R.DtInter, '%Y-%m-%d') DtInter 
FROM ...
GROUP BY ... DtInter
ORDER BY DtInter DESC

try this –

INSERT INTO ...
SELECT ...
, STR_TO_DATE(R.DtInter, '%Y-%m-%d') booyah
FROM ...
GROUP BY ... booyah
ORDER BY booyah DESC

Miguel61 · July 21, 2011, 2:27pm

I’m sorry, same error:

Query:


INSERT INTO  
_Reg_L   
(  
  xID  
, DtUpdate 
, CFT_Cod  
, Orig_Int 
, DtInter  
, `A20(Min)`  
, A16_17   
)  
SELECT  
 0  
, R.DtUpdate 
, C.CFT_Cod  
, CASE  
  WHEN R.Art='51M'  THEN '51M'  
  WHEN R.Art='450'  THEN 'MT'  
  ELSE 'BT'  
  END Orig_Int  
, STR_TO_DATE(R.DtInter, '%Y-%m-%d') booyah 
, SUM(IF(R.CDI='P', R.C_BT * `DNF8 (sec)`/60, 0)) A20  
, SUM(IF(R.CDI In('P','B'), R.C_BT, 0)) A16_17
  
FROM _Reg R  
INNER JOIN Org C ON R.CFT_Cod = Org.CFT_Cod_old  

WHERE 1 AND  
(  
(R.Pre='N' Or IsNull(R.Pre))  
AND R.E_Cod Like 'T%'  
AND R.C_I&lt;&gt;'F'  
AND R.Pert='N'  
AND R.Art In ('51M','450','41B','460','475','476')  
)   

  GROUP BY  
  C.CFT_Cod  
, Orig_Int  
, booyah

HAVING A20 &gt; 0 OR A16_17 &gt; 0  
ORDER BY booyah DESC;

[Err] 1292 - Truncated incorrect date value: ‘2011-01-25 09:07:00’

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50151
Source Host           : localhost:3306
Source Database       : _db

Target Server Type    : MYSQL
Target Server Version : 50151
File Encoding         : 65001

Date: 2011-07-21 16:19:03
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `_reg`
-- ----------------------------
DROP TABLE IF EXISTS `_reg`;
CREATE TABLE `_reg` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DtUpdate` date DEFAULT NULL,
  `CFT_Cod` varchar(255) DEFAULT NULL,
  `E_Cod` varchar(255) DEFAULT NULL,
  `DtInter` datetime DEFAULT NULL,
  `Pre` varchar(255) DEFAULT NULL,
  `CDI` varchar(255) DEFAULT NULL,
  `C_I` varchar(1) DEFAULT NULL,
  `Pert` varchar(255) DEFAULT NULL,
  `DNF8 (sec)` decimal(10,2) DEFAULT NULL,
  `R.Art` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `CFT_Cod` (`CFT_Cod`),
  KEY `CDI` (`CDI`),
  KEY `Pre` (`Pre`),
  KEY `C_I` (`C_I`),
  KEY `Pert` (`Pert`),
  KEY `Art` (`Art`),
  KEY `E_Cod` (`E_Cod`),
  KEY `DtInter` (`DtInter`)
) ENGINE=InnoDB AUTO_INCREMENT=4063171 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of _reg
-- ----------------------------


/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50151
Source Host           : localhost:3306
Source Database       : _db

Target Server Type    : MYSQL
Target Server Version : 50151
File Encoding         : 65001

Date: 2011-07-21 16:29:47
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `_Reg_L`
-- ----------------------------
DROP TABLE IF EXISTS `_Reg_L `;
CREATE TABLE `_Reg_L` (
  `xID` int(10) NOT NULL AUTO_INCREMENT,
  `DtUpdate` date DEFAULT NULL,
  `CFT_Cod` varchar(255) DEFAULT NULL,
  `Orig_Int` varchar(255) DEFAULT NULL,
  `DtInter` date DEFAULT NULL,
  `A20(Min)` decimal(10,2) DEFAULT NULL,
  `A16_17` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`xID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of _Reg_L
-- ----------------------------

r937 · July 21, 2011, 4:17pm

thanks for the table layouts, that helps a lot

since the source column DtInter in the _Reg table is DATETIME and not VARCHAR, you should not use STR_TO_DATE on it, but instead, use DATE

Miguel61 · July 21, 2011, 4:38pm

Rudy:

With your last modify now I have affected 204.211 rows and don’t have records when the field Orig_Int is null.

The new number of records 204.211 imported in MySQL is much closer and trusted compared with 204.786 (query access).

You think are other errors in this translation ?
Thanks you very much for your appreciated help.

Query:


INSERT INTO  
_Reg_L   
(  
  xID  
, DtUpdate 
, CFT_Cod  
, Orig_Int 
, DtInter  
, `A20(Min)`  
, A16_17   
)  
SELECT  
 0  
, R.DtUpdate 
, C.CFT_Cod  
, CASE  
  WHEN R.Art='51M'  THEN '51M'  
  WHEN R.Art='450'  THEN 'MT'  
  ELSE 'BT'  
  END Orig_Int  
, DATE(R.DtInter) booyah 
, SUM(IF(R.CDI='P', R.C_BT * `DNF8 (sec)`/60, 0)) A20  
, SUM(IF(R.CDI In('P','B'), R.C_BT, 0)) A16_17
  
FROM _Reg R  
INNER JOIN Org C ON R.CFT_Cod = Org.CFT_Cod_old  

WHERE 1 AND  
(  
(R.Pre='N' Or IsNull(R.Pre))  
AND R.E_Cod Like 'T%'  
AND R.C_I&lt;&gt;'F'  
AND R.Pert='N'  
AND R.Art In ('51M','450','41B','460','475','476')  
)   

  GROUP BY  
  C.CFT_Cod  
, Orig_Int  
, booyah

HAVING A20 &gt; 0 OR A16_17 &gt; 0  
ORDER BY booyah DESC;

r937 · July 21, 2011, 5:10pm

i have no idea

when you ran that query, did it produce any error messages?

r937 · July 21, 2011, 4:46pm

i’m sorry, i don’t know what you are asking

Miguel61 · July 21, 2011, 4:54pm

You think are other errors in this translation ?

Miguel61 · July 21, 2011, 5:27pm

No, nothing error.
Only slight inconsistency between the rows written by Access and the rows written by MySQL.

204.211 imported by MySQL
204.786 imported by Access

Would be interesting know where to go this 575 rows of difference… thank you

r937 · July 21, 2011, 5:40pm

you need to do some data investigation

Miguel61 · July 21, 2011, 5:41pm

Yes, but here was given a big help… thank you!