RE: cast float as varchar show scientific notation result

[Anoop Sharma]

-->A frontend client may choose to display the approx float value in other 
convenient formats, like 1.23. This is what you see from trafci output.
It depends on the client. trafci chooses to display it like that by default. 
Maybe there is a setting to display in float format.

sqlci(another client) displays it in native approx. format.
      Apache Trafodion Conversational Interface 2.3.0
      Copyright (c) 2015-2017 Apache Software Foundation
      >>select 1.23e10 from dual;

      (EXPR)
      -------------------------

       1.23000000000000000E+010

      --- 1 row(s) selected.
      >>

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan....@esgyn.cn]
Sent: Monday, December 11, 2017 9:56 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: cast float as varchar show scientific notation result

Hi Anoop,

Thanks for your answer. But why the output of "select * from test7" is 123.4, 
rather than a scientific value such as 1.23400000000000000E+002?


Best regards,
Yuan

-----Original Message-----
From: Anoop Sharma [mailto:anoop.sha...@esgyn.com]
Sent: Tuesday, December 12, 2017 1:33 PM
To: 
dev@trafodion.incubator.apache.org<mailto:dev@trafodion.incubator.apache.org>
Subject: RE: cast float as varchar show scientific notation result

A value stored in a float, real or double precision column is stored in 
approximate numeric format. That means that the stored value may be approximate 
especially if the scale exceeds the max scale for that column or if 
precision/scale is truncated.

It doesn't matter the format of value that was inserted(10 or 10.234 or 2e3), 
they are all stored as approximate datatype. The selected value may also not be 
exactly the same as what was inserted.

When this float value is converted to char/varchar, the default conversion 
format is approximate or scientific. (<sign>N.NE<sign>MM). This matches the 
value that was stored.

A frontend client may choose to display the approx float value in other 
convenient formats, like 1.23. This is what you see from trafci output.

The conversion error is also correct as the cast is expecting the target to be 
able to contain all possible float values.

To convert and display an approx/float values as an exact numeric, one would 
need to cast it to a numeric with scale before casting it to char/varchar.
Something like:
  select cast(cast(a as numeric(10,3)) as varchar(30)) from test7;

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan....@esgyn.cn]
Sent: Monday, December 11, 2017 8:01 PM
To: 
dev@trafodion.incubator.apache.org<mailto:dev@trafodion.incubator.apache.org>
Subject: cast float as varchar show scientific notation result

Hi Trafodioneers,

I tried to cast float value 123.4 to varchar, but the output format is 
scientific notation format. Is it by design? I think if the output 123.4 is 
better, agree?

SQL>create table test7( a float);
SQL>insert into test7 values(123.4);
SQL>select * from test7;

A
------------------------
                   123.4
SQL>select cast(a as varchar(30)) from test7;

(EXPR)
------------------------------
1.23400000000000000E+002

SQL>select cast(a as varchar(9)) from test7;

(EXPR)
---------
1.2E+002

--- 1 row(s) selected.

SQL>select cast(a as varchar(8)) from test7;

*** ERROR[8402] A string overflow occurred during the evaluation of a character 
expression. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) 
Source Value:1.23400000000000000E+002 to Target Type:VARCHAR(REC_BYTE_V_ASCII). 
[2017-12-12 11:55:47]


Best regards,
Yuan