Mailing List
Home
MySQL General - General MySQL discussion
MaxDB - Everything about MaxDB, formerly known as SAP DB
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
ODBC - ODBC with the MySQL Connector/ODBC driver
Perl - Perl support for MySQL with DBI and DBD::mysql
MySQL++ - Programming with the C++ API to MySQL
Subjects
• mysql openssl Question
• mysql:it 's a db not a dbms how it 's possible?!
• Does the binary log enabling affect the MySQL performances?
• Strange behavior, Table Level Permission
• FULLTEXT query format question
• Preventing Duplicate Entries
• Comparing and writing out BLOBS
• Executing MySQL Commands From Within C Program
• Can 't access mysql after kernel upgrade
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Downgrade Mysql from 4 to 3 23
• MySQL Cluster Software
• mysql test 4 1 fails with the gis test
• ERROR 2002: Can 't connect to local MySQL server through socket
• Getting Identity after INSERT
• Update one field with more fields from another table
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• mysql have same function mthod as Oracle decode()
 
Search:  
Power your search with and, or, +, -, or "some phrase" operators.
Select double value

Select double value

2004-06-07       - By Michael Stassen

 Back
Reply:     1     2     3     4     5  


Paul McNeil wrote:

> Good morning to all.
>
> I have a problem with a workaround but I wanted to know if others have run
> into this.

Are you sure the problem is with mysql? If so, which version do you have?
I have 4.0.20, and I get different (expected) results.

> Table DATA
> Column strength [double]

CREATE TABLE data (strength DOUBLE);
INSERT INTO data VALUES (3.256498), (0), (2.71828), (NULL), (0.00000);

> When I select strength from DATA and the result is a non zero amount it
> returns correctly
>
> 3.256498
>
> however if it is a 0 amount I get
>
> 0.00000000

mysql > SELECT strength FROM data;
+-- ---- --+
| strength |
+-- ---- --+
| 3.256498 |
| 0 |
| 2.71828 |
| NULL |
| 0 |
+-- ---- --+
5 rows in set (0.18 sec)

> The problem is that in my java.sql.ResultSet.getDouble( "strength ") a zero
> amount throws a number format exception. SO, I placed a conditional....
>
> SELECT
> CASE
> WHEN strength IS NULL OR strength = 0
> THEN 0
> ELSE
> strength
> END
>
> This, however seems to truncate the result so that a zero return results in
> 0 BUT a return of 3.1236564 results in 3.

mysql > SELECT CASE WHEN strength IS NULL OR strength = 0
- > THEN 0 ELSE strength END AS strength
- > FROM data;
+-- ---- --+
| strength |
+-- ---- --+
| 3.256498 |
| 0 |
| 2.71828 |
| 0 |
| 0 |
+-- ---- --+
5 rows in set (0.27 sec)

> Bummer. Finally I had to restructure my conditional...
>
> SELECT
> CASE
> WHEN strength IS NOT NULL AND strength != 0
> THEN strength
> ELSE
> '0 '
> END

You shouldn 't need to quote 0, and "IS NOT NULL " is redundant here, as
strength != 0 will evaluate to FALSE when strength is NULL.

mysql > SELECT CASE WHEN strength != 0 THEN strength ELSE 0 END strength
- > FROM data;
+-- ---- --+
| strength |
+-- ---- --+
| 3.256498 |
| 0 |
| 2.71828 |
| 0 |
| 0 |
+-- ---- --+
5 rows in set (0.27 sec)

> I feel that this type of data manipulation shouldn 't need to be done. Is

Agreed.

> this a bug or normal for a return type of double?

For me, it is neither. Are you using an older version? Otherwise, I
suspect the problem lies elsewhere.

Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql @(protected)