Solved

MySQL Error: Data truncated for column

Posted on 2011-09-15
3
741 Views
Last Modified: 2012-05-12
I am trying to do an UPDATE with:

UPDATE trades_entered
SET MaxPercGainEpM = format( ( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price )  * 100 ) , 2);

It updates but I get:

Note    | 1265 | Data truncated for column 'MaxPercGainEpM' at row 244
Note    | 1265 | Data truncated for column 'MaxPercGainEpM' at row 164
Warning | 1048 | Column 'MaxPercGainEpM' cannot be null

Where:
`MaxPercGainEpM`     DECIMAL( 12, 4 ) NOT NULL DEFAULT 0

I then tried:

UPDATE trades_entered
SET MaxPercGainEpM = if ( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price)  * 100 is null , 999 ,
                          format( ( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price )  * 100 ) , 2)
                        )  
;
This got rid of:
Warning | 1048 | Column 'MaxPercGainEpM' cannot be null
But I am still getting:
Note  | 1265 | Data truncated for column 'MaxPercGainEpM' at row 244 |
Note  | 1265 | Data truncated for column 'MaxPercGainEpM' at row 164 |

So I have two questions:
1) IS there a way I can query the table "trades_entered" for row 244 so I can look at the data values?

2) How can I get rid of the warnings?

Thanks in advance.
0
Comment
Question by:John_2357
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36545131
Hi.

You can do a select for rows containing NULLs to find the culprit.

SELECT *
FROM trades_entered
WHERE fdate IS NULL OR exitDateEpM IS NULL OR Sym IS NULL OR entry_price IS NULL

For the truncation error, you are likely getting values that go outside bounds of your decimal. You can try running MAX() and MIN() on the calculation and see what your range is like. It won't tell the whole story, but may shed some light.

SELECT
MAX(( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price )  * 100 )),
MIN(( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price )  * 100 )),
MAX(CHARACTER_LENGTH( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price )  * 100 ))
FROM trades_entered
;

The last column may be telling if it is greater than 12. :)

NOTE: If you are storing a DECIMAL(12, 4), I would remove FORMAT() which is formatting the content as string rounded to 2 decimal places with commas. Use ROUND() function if you are just wanting 2 decimal rounding.

You can use COALESCE() or ISNULL() to wrap columns that may be null and default them appropriately.

e.g.,

COALESCE(( ( (max_value(fdate, exitDateEpM, Sym) - entry_price) / entry_price )  * 100 ), 0)

Hope that helps!
0
 
LVL 1

Author Closing Comment

by:John_2357
ID: 36893460
Sorry for the delay in closing this - I have been in the hospital with my Dad - just found out he has terminal cancer. Again thanks for your help.

John
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36894288
You are most welcome! Sorry to hear about your father. No worries at all, but especially given you have way more important things to focus on. Good luck!

Respectfully yours,

Kevin
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question