SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem translate query from Access to MySQL

    Problem translate query from Access to MySQL

    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 ):
    Code:
    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 ):
    Code:
    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<>'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 > 0 OR A16_17 > 0  
    ORDER BY DtInter DESC;

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Don't know about the extra lines, but this
    Code:
    IIf([R].[Art]='51M','51M',IIf([R].[Art]='450','MT','BT')) AS Orig_Int,
    shouldn't become
    Code:
    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
    Code:
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in access query, one of the GROUP BY columns is a date

    in mysql query, the same GROUP BY column is a datetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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<>'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 > 0 OR A16_17 > 0  
    ORDER BY DtInter DESC;

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I need write in the table name `_Reg_L ` only value of the date (no time format).
    if that's what you need to do, then i would suggest that you change this --
    Code:
    STR_TO_DATE(R.DtInter, '%Y-%m-%d %H:%i:%s') DtInter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Ok, I try this with your suggestion, but I have error:

    Query:
    Code:
    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<>'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 > 0 OR A16_17 > 0  
    ORDER BY DtInter DESC;
    [Err] 1292 - Truncated incorrect date value: '2011-01-25 09:07:00'

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    INSERT INTO ...
    SELECT ...
    , STR_TO_DATE(R.DtInter, '%Y-%m-%d') DtInter 
    FROM ...
    GROUP BY ... DtInter
    ORDER BY DtInter DESC
    try this --
    Code:
    INSERT INTO ...
    SELECT ...
    , STR_TO_DATE(R.DtInter, '%Y-%m-%d') booyah
    FROM ...
    GROUP BY ... booyah
    ORDER BY booyah DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, same error:

    Query:
    Code:
    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<>'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 > 0 OR A16_17 > 0  
    ORDER BY booyah DESC;
    [Err] 1292 - Truncated incorrect date value: '2011-01-25 09:07:00'



    Code:
    /*
    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
    -- ----------------------------

    Code:
    /*
    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
    -- ----------------------------

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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<>'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 > 0 OR A16_17 > 0  
    ORDER BY booyah DESC;

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i don't know what you are asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm sorry, i don't know what you are asking
    You think are other errors in this translation ?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    You think are other errors in this translation ?
    i have no idea

    when you ran that query, did it produce any error messages?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you need to do some data investigation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Yes, but here was given a big help... thank you!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •