Float/Decimal values from query are rounded to 0.001

Hey all,

I am trying to pull float and or decimal datatypes via a named query, but Ignition rounds to 0.001, when the values I need are 1.0E-10. Originally I used the float type in the DB, then changed to decimal(12,11). (SQL Server)

Aside from multiplying the incoming decimal value by 1.0E+10, is there something else I can do?

Thanks.

Are you sure the value you're trying to pull has an exact float/double representation?

SQL databases support, or at least claim to, arbitrary precision decimals, but those ultimately get mapped down to Java primitive data types, which are (for floating point) either 32 bit or 64 bit IEEE-754 floating point.

Yeah, don't use decimal. Ignition doesn't have a matching data type (would be java's BigDecimal).

Well, I started off by using the default for float, which is float(53). Then changed it to float(24), ISO synonym for real. Then changed it to decimal(12,11).

Data with decimal:

Float(53):

Query results:
image

Not sure where you are looking at the query results, but try changing the number format on that component. Either to none or scientific in Perspective. Maybe something like 0,0.#############

The query results come from the named query testing screen. I suppose I could have captured more of the window so it was more intuitive.

Edit: And if I pull the dataset into a custom dataset property, same values.

So, if you have floats stored in your DB, and you use a named query to retrieve those values, how do you get those values to come back accurately into the query?

The query just does not seem to want to handle floats with more than 3 decimal places.

And, @PGriffith , I'm not quite sure what to do with these exposed floats. How can I know if the value has an exact representation? For instance, if I type in the value 0.0000012, and press enter, the displayed result is: 0.0000012000000424450263381. Is this what you mean by exact representation? Meaning the actual value is different, but that raises the question: How does this happen? And then, how do I get values stored to the 8th decimal place back from the DB?

OR, maybe the actual value is retrieved, but it is not displayed properly because of a type mismatch or no exact representation?

However, I am looking at this chart:

Here is the data:

Obviously the values in the dataset editor are limited to 3 decimal places, but we can see that the chart picks up at least one extra decimal place, and rounding to that fourth decimal.

Here is the data stored in the table:

From another chart:

What I think I am seeing, in the chart, is that if the values fall under a certain "threshold", say less than 0.0001, then it displays those values in scientific notation, as the last picture shows.

To summarize:

  • I am not why the values in the dataset editor and named query editor do not display more than 3 decimal places.
  • The actual values do come through to the chart.
  • I am not sure how to apply the "Float Exposed" information.

This post turned out to be more of a tracing out my thoughts and uncovering some oddities that were not clear before. So, I am going to mark this as solved, EXCEPT for my question on the Exposed Floats.

Thank you all!