MySQL Error: Data truncated for column

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.
LVL 1
John_2357Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
John_2357Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.