Solved

MySQL Error: Data truncated for column

Posted on 2011-09-15
3
743 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

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 60

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

696 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