Table number format has no effect?

Archived Topics
1

I have a data column with values like 703.1667. And it shows “703.1667” no matter what format I tell it to use suach as “#,##0” or “#,##0%” What am i missing to make the data conform to the format?

I am pulling the data from a My SQL data base with the following query:

SELECT goodlbs/schHrs as lbsPerSchHour
FROM OEE_EFoutputByRun as lbs
LEFT JOIN OEE_TimeSummaryByRun AS timeSum
ON timeSum.idMachineRuns = lbs.idMachineRuns
WHERE schHrs > 0

2

The problem is that the values are coming back as strings from MySQL and we are expecting a floating point. It has to do with MySQL returning the wrong data type when doing goodlbs/schHrs in the query. Check the dataset using the dataset viewer in the designer to make sure that this is the case (data type is wrong).

If so you can either do a number format in the query using MySQL’s format function. http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html

Or you can create a function in MySQL that returns the value as a double no matter what. For example:CREATE FUNCTION `toFloat`(inParam VARCHAR(255)) RETURNS double BEGIN RETURN inParam; ENDYou can wrap your goodlbs/schHrs with the function toFloat(goodlbs/schHrs). Let us know what you find.