1

Got a little issue here. I'm periodically making an automated update of currencies in a MySQL table. Part of the generated query is :

UPDATE ara_curr SET 
curr_xchange=REPLACE(CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)),'.',','),
curr_rev_xchange=REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') 
WHERE curr_name='IDR';

I get the values from a online service. However this query returns 

Out of range value for column (null) at row 1

However if I break it down the conversions return these

REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') = 6221,27000
CONVERT('1.60739',DECIMAL(9,5)) = 1,60739
CONVERT('10000',DECIMAL(9,5)) = 9999,99999
CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)) = 0,00016

And if I try to run the query directly with those values e.g.

UPDATE ara_curr SET curr_xchange='0,00016', curr_rev_xchange='6221,27000' WHERE curr_name='IDR';

It runs perfectly OK!

Any ideas about this?

nnikolov06
  • 183
  • 3
  • 11

2 Answers2

0

What datatypes are curr_xchange and curr_rev_xchange?

If i run your query it returns the result as binary data. Maybe you can try this modification with a CAST around the results.

UPDATE ara_curr SET 
curr_xchange=CAST(REPLACE(CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)),'.',',') AS CHAR),
curr_rev_xchange=CAST(REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') AS CHAR) 
WHERE curr_name='IDR';
Martin
  • 1,184
  • 8
  • 24
  • Well the curr_xchange and curr_rev_xchange are VARCHAR. But casting doesn't work too. I'm not exactly sure why they were not set as DECIMAL but no big deal. – nnikolov06 Apr 04 '12 at 07:59
0

Try using CAST instead of CONVERT:

UPDATE  ara_curr
SET     REPLACE(CAST(CAST('1.60739' AS DECIMAL(9, 5)) / CAST('10000' AS DECIMAL(9, 5)) AS DECIMAL(9, 5)), '.', ',') AS curr_xchange
        , REPLACE(CAST('6221.27' AS DECIMAL(9, 5)), '.', ',') AS curr_rev_xchange
WHERE   curr_name = 'IDR';

Also, you may run into some trouble casting 10000 as a DECIMAL(9, 5) as it exceeds the allowed range. You may want to cast that to DECIMAL(10, 5) instead.

pete
  • 24,141
  • 4
  • 37
  • 51
  • I've changed the datatypes from varchar to decimal some time ago. So casting to varchar issues have disappeared. Haven't though about problems with 9,5 decimal cast on 10000 but you are absolutely right. It outputs to 9999,99999. Thankfully this was one of the currencies that were removed because they weren't going to be used. We stuck with 5-6 (probably) currencies which do not have such high conversion values. Thanks for the advice though. Will note it and (hopefully) I'll remember the next time I come about such an issue. – nnikolov06 Oct 28 '12 at 18:05