Solved

MySQL Error: Data truncated for column

Posted on 2011-09-15
3
739 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now