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 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
