How to disable scientific notation for small numbers?

[jdorlon]

Hello,

I know that we can the EnableNumbers option to avoid scientific notation for large numbers, but I still see scientific notation for very small numbers, for example the result of this query....

Code: Select all

select
  cast(0.01 as number(7,6)) as n1
, cast(0.001 as number (7,6)) as n2
, cast(0.0001 as number(7,6)) as n3
, cast (0.00001 as number(7,6)) as n4
, cast (0.000001 as number(7,6)) as n5
from dual;

Is there a way to make these all come back in non-scientific notation? Casting as a varchar2 is not an option - this was just a simple example. In our application, the user writes the queries, not the application.

Thank you,

-John

[bork]

Hello

You confuse storing numbers in the memory and numbers text representation. For example 0.000001 and 1E-6 is the same number and it is stored in the memory identically.

When you try to show this number:

Code: Select all

 ShowMessage(OraQuery1.fieldbyname('n5').AsString);

Then Delphi call the following function in the DB unit:

Code: Select all

function TFloatField.GetAsString: string;
var
  F: Double;
begin
  if GetData(@F) then Result := FloatToStr(F) else Result := '';
end;

This function returns the following text representation: 1E-6

If you don't want to use standard Delphi conversion of Float data to text, then you can use the following code:

Code: Select all

  ShowMessage(FormatFloat('0.##########', OraQuery1.fieldbyname('n5').AsFloat));

Also you can specify format for TFloatField when it will be displayed in the TDBGrid:

Code: Select all

  TFloatField(OraQuery1.fieldbyname('n5')).DisplayFormat :='0.##########';

ODAC components just store data as number in the memory and doesn't convert it to text representation.

[jdorlon]

Hello,

Thank you. I will use the DisplayFormat property.

-John

[jdorlon]

Hello,

I know that it has been a while since the last message in this topic, but I have a new problem which is related to it.

After the last message in this thread, I started using the DisplayFormat property as you suggested, and then some time later, I discovered that if field was declared as NUMBER(14) or NUMBER(15) and the value was very large and odd, then decimal values were getting returned. On removing the DisplayFormat code, I discovered that the original problem of very small numbers being shown in scientific notation was no longer existed. So I guess some change you made in ODAC corrected that. That's great and no problem so far.

Now, I have discovered that if I create a table like this:
create table num_test(num_col number, num1310_col number(13,10));

and insert a row like this:
insert into num_test(num_col, num1310_col)
values (1/power(10,7), 1/power(10,7));
commit;

then select from this table using a TSmartQuery with the EnableNumbers property set to TRUE on the TOraSession, then I see one the NUMBER column in non-scientific notation, and the NUMBER(13,10) column in scientific notation.

Can a change be made in ODAC so that the text Field.AsString shows both of these fields in non-scientific notation?

Thanks,

John

[AlexP]

Hello,

It is correct behavior. If you set the EnableNumbers property to True, then NUMBER fields, that have precision more than 15, will be defined as Number fields.
Others, with lower precision) - as float fields. In order to make the result of both fields the same when using the AsString method, you should decrease the value of the FloatPrecision global variable (declared in the OraClasses module) to a value less than precision of your field (12).

[jdorlon]

OK, thank you.

[AlexP]

You are welcome. Feel free to contact us if you have any further questions.