SitePoint Sponsor

User Tag List

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

    IFNULL() Function

    Hi there, I need your help.

    This is mysql query INSERT INTO with error:

    Code:
    [SQL] INSERT INTO
    tbl_1
    (
      xID
    , Events_A
     )
    SELECT
      0
    , IFNULL(Events_A, 0) 
    FROM tbl_2 
    WHERE 1 
    LIMIT 0,20;
    [Err] 1366 - Incorrect decimal value: '' for column 'Events_A' at row 1
    The field 'Events_A' is decimal type 10,0 allow null.
    In the tbl_2 all values of the field 'Events_A' is null or empty.

    I think use the IFNULL() Function:
    If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

    Why error?
    Thanks in advance.

  2. #2
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    By using decimal 10,0, you are saying there should be no numbers after the decimal point. You are trying to put 0.00 in a space where 0 is valid.
    Ryan B | My Blog | Twitter

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Events_A decimal 10 2
    Code:
    [SQL] INSERT INTO
    tbl_1
    (
      xID
    , Events_A
     )
    SELECT
      0
    , IFNULL(Events_A, 0.00) 
    FROM tbl_2 
    WHERE 1 
    LIMIT 0,20;
    [Err] 1366 - Incorrect decimal value: '' for column 'Events_A' at row 1

  4. #4
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Try making it 3,2 versus 10,2. (What you have says calc to 10 places but only keep two)
    Try wrapping 0.00 in single or double quotes.
    Ryan B | My Blog | Twitter

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rguy84 View Post
    Try making it 3,2 versus 10,2. (What you have says calc to 10 places but only keep two)
    Try wrapping 0.00 in single or double quotes.
    Many thks x your help, but the problem is persistent...

    Events_A: decimal , 3, 2

    Code:
    [SQL] INSERT INTO
    tbl_1
    (
      xID
    , Events_A
     )
    SELECT
      0
    , IFNULL(Events_A, '0.00') 
    FROM tbl_2 
    WHERE 1 
    LIMIT 0,20;
    [Err] 1366 - Incorrect decimal value: '' for column 'Events_A' at row 1

  6. #6
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    What is this query trying to do?
    Ryan B | My Blog | Twitter

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    ... but the problem is persistent...
    please do a SHOW CREATE TABLE for both tables
    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
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Ok:

    Code:
    /*
    Navicat MySQL Data Transfer
    
    Source Server         : _MySQL
    Source Server Version : 50045
    Source Host           : localhost:3306
    Source Database       : db
    
    Target Server Type    : MYSQL
    Target Server Version : 50045
    File Encoding         : 65001
    
    Date: 2011-07-18 18:48:08
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for `tbl_1`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_1`;
    CREATE TABLE `tbl_1` (
      `xID` int(10) NOT NULL auto_increment,
      `Events_A` varchar(255) default NULL,
      `Events_B` varchar(255) default NULL,
      `Events_C` varchar(255) default NULL,
      `Events_TOT` varchar(255) default NULL,
      `Events_NL` varchar(255) default NULL,
      `Events_CL` varchar(255) default NULL,
      PRIMARY KEY  (`xID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6103 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_1
    -- ----------------------------
    INSERT INTO tbl_1 VALUES ('1', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('2', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('3', '', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('4', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('5', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('6', '', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('7', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('8', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('9', '', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('10', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('11', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('12', '', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('13', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('14', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('15', '', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('16', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('17', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('18', '', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('19', '', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('20', '', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('21', '4', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('22', '9', '8,095', '8,276', '', '', '');
    INSERT INTO tbl_1 VALUES ('23', '6', '2', '3', '3', '', '');
    INSERT INTO tbl_1 VALUES ('24', '4', '1', '2', '', '', '0');
    INSERT INTO tbl_1 VALUES ('25', '5', '1', '2', '', '', '0');
    
    
    
    /*
    Navicat MySQL Data Transfer
    
    Source Server         : _MySQL
    Source Server Version : 50045
    Source Host           : localhost:3306
    Source Database       : db
    
    Target Server Type    : MYSQL
    Target Server Version : 50045
    File Encoding         : 65001
    
    Date: 2011-07-18 18:47:57
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for `tbl_2`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_2`;
    CREATE TABLE `tbl_2` (
      `xID` int(10) NOT NULL auto_increment,
      `Events_A` decimal(3,2) default NULL,
      PRIMARY KEY  (`xID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_2
    -- ----------------------------
    I need insert in tbl_2 all values `Events_A` of the tbl_1, `Events_A` in tbl_1 is varchar 255 and null (empty).

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's weird -- you're copying a 0 into an auto_increment column (always a dicey proposition), and a decimal into a varchar

    try this --
    Code:
    INSERT 
      INTO tbl_1
         ( xID
         , Events_A )
    SELECT 0
         , CONCAT('',COALESCE(Events_A,'0.00')) 
      FROM tbl_2
    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
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Code:
    INSERT 
      INTO tbl_1
         ( xID
         , Events_A )
    SELECT 0
         , CONCAT('',COALESCE(Events_A,'0.00')) 
      FROM tbl_2
    [Err] 1366 - Incorrect decimal value: '' for column 'Events_A' at row 1


  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I need insert in tbl_2 all values `Events_A` of the tbl_1
    okay, now i'm confused

    which table are you actually trying to insert into?

    also, please give some sample data for tbl_2
    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
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Ok, start over.

    My data source is txt file, field delimiter `Tab`.

    I import this txt file in db mysql, with `LOAD DATA INFILE` function, and to avoid import errors I have create one temporary table (tbl_1), where all fields are varchar 255, allow null.

    The `LOAD DATA INFILE` in tbl_1 working.

    Now I need import in tbl_2 all values of the temporary tbl_1 and I have started debug with first field: `Events_A`.

    The problem is that in the temporary tbl_1 the field `Events_A` can be null (empty) and the INSERT INTO query in the tbl_2 response with error.

    The tbl_2 is the final copy of the tbl_1, but with fields correctly for each value (varchar, decimal, etc).

    Can you help me?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i can see now where the confusion came from

    you posted INSERT INTO tbl_1 ... SELECT ... FROM tbl_2

    what you actually want is INSERT INTO tbl_2 ... SELECT ... FROM tbl_1

    see the difference?
    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
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thks, I see the difference... but:

    Code:
    [SQL] INSERT INTO
    tbl_2
    (
      xID
    , Events_A
     )
    SELECT
      0
    , CONCAT('',COALESCE(Events_A,'0.00'))
    FROM tbl_1
    WHERE 1 
    LIMIT 0,20;
    [Err] 1366 - Incorrect decimal value: 
    '' for column 'Events_A' at row 1

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, yeah

    you are now trying to insert from a VARCHAR into DECIMAL (rather than the other way around)

    therefore, you should ~not~ be trying to insert a string

    try it without the CONCAT and without the quotes around 0.00, i.e. COALESCE(Events_A,0.00)

    you may still also need further remedial action if the value in tbl_1 is neither NULL nor a valid decimal representation
    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
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks but still error:

    Code:
    [SQL] INSERT INTO
    tbl_2
    (
      xID
    , Events_A
     )
    SELECT
      0
    , (COALESCE(Events_A, 0.00))
    FROM tbl_1
    WHERE 1 
    LIMIT 0,20;
    [Err] 1366 - Incorrect decimal value: '' for column 'Events_A' at row 1

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's a tip: don't (use ((extra) parentheses) needlessly)

    apparently your uploaded data has zero-length strings

    try
    Code:
    COALESCE(NULLIF(Extra_A,''),0.00)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Many thanks !!!
    Code:
    [SQL] INSERT INTO
    tbl_2
    (
      xID
    , Events_A
     )
    SELECT
      0
    , COALESCE(NULLIF(Events_A,''),0.00)
    FROM tbl_1
    WHERE 1 
    LIMIT 0,20;
    Affected rows: 20
    Time: 0.062ms


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
  •