Solved

MySQL Error: Data truncated for column

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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

690 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