IFNULL() Function

Hi there, I need your help.

This is mysql query INSERT INTO with error:

[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.

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.

Events_A decimal 10 2


[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

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… :frowning:

Events_A: decimal , 3, 2

[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

What is this query trying to do?

please do a SHOW CREATE TABLE for both tables

Ok:


/*
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).

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 –

INSERT 
  INTO tbl_1
     ( xID
     , Events_A )
SELECT 0
     , CONCAT('',COALESCE(Events_A,'0.00')) 
  FROM tbl_2 
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

:sick:

okay, now i’m confused

which table are you actually trying to insert into?

also, please give some sample data for tbl_2

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?

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?

Thks, I see the difference… but:


[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

well, yeah :slight_smile:

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

thanks but still error:

[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

here’s a tip: don’t (use ((extra) parentheses) needlessly)

apparently your uploaded data has zero-length strings

try

COALESCE(NULLIF(Extra_A,''),0.00)

Many thanks !!! :slight_smile:


[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